如何在SQL Server中将XML数据转换为行列数据

I have a requirement where I have XML data in database, which I need to pull in the form of row and Column from database. My XML is like

我有一个在数据库中有XML数据的需求，我需要从数据库中提取行和列的形式。我的XML是像

<Equipment>
<EquipmentRecord>
<EquipmentType>E</EquipmentType><MfgName>APPLE iPH564WHT</MfgName><Model>885909600205</Model><ActDate>10/26/2012</ActDate><CancelDate />
</EquipmentRecord>
<EquipmentRecord>
<EquipmentType>E</EquipmentType><MfgName>SAMSUNG D710 HANDSET KIT</MfgName><Model>SPHD710KIT</Model><ActDate>09/04/2012</ActDate><CancelDate>10/01/2012</CancelDate>
</EquipmentRecord>
<EquipmentRecord>
<EquipmentType>E</EquipmentType><MfgName>BLACKBERRY 9670 PURPLE</MfgName><Model>SPRIM9670P</Model><ActDate>08/22/2012</ActDate><CancelDate>08/25/2012</CancelDate>
</EquipmentRecord>
</Equipment>

And I want data to be pulled up like

我希望数据像这样被提取出来

EquipmentType   MfgName                 Model           ActDate     CancelDate
E             APPLE iPH564WHT           885909600205    10/26/2012  
E             SAMSUNG D710 HANDSET KIT  SPHD710KIT      09/04/2012  10/01/2012
E             BLACKBERRY 9670 PURPLE    SPRIM9670P      08/22/2012  08/25/2012

is there any simple way to this in SQL server?

在SQL server中有什么简单的方法来实现这一点吗?

I tried and it worked

我试过了，结果成功了

DECLARE @doc XML
SET @doc = 'above XML'  

CREATE TABLE #EquipmentType (ID INT IDENTITY(1,1), EquipmentType varchar(100))
INSERT INTO #EquipmentType(EquipmentType)
SELECT EquipmentDetails.value('.','varchar(100)') as EquipmentType
FROM @doc.nodes('/Equipment/EquipmentRecord/EquipmentType/.') Equipment(EquipmentDetails)

CREATE TABLE #MfgName (ID INT IDENTITY(1,1), MfgName varchar(500))
INSERT INTO #MfgName(MfgName)
SELECT EquipmentDetails.value('.','varchar(500)') as MfgName
FROM @doc.nodes('/Equipment/EquipmentRecord/MfgName/.') Equipment(EquipmentDetails)

CREATE TABLE #Model (ID INT IDENTITY(1,1), Model varchar(500))
INSERT INTO #Model(Model)
SELECT EquipmentDetails.value('.','varchar(500)') as Model
FROM @doc.nodes('/Equipment/EquipmentRecord/Model/.') Equipment(EquipmentDetails)

CREATE TABLE #ActDate (ID INT IDENTITY(1,1), ActDate varchar(500))
INSERT INTO #ActDate(ActDate)
SELECT EquipmentDetails.value('.','varchar(500)') as ActDate
FROM @doc.nodes('/Equipment/EquipmentRecord/ActDate/.') Equipment(EquipmentDetails)

CREATE TABLE #CancelDate (ID INT IDENTITY(1,1), CancelDate varchar(500))
INSERT INTO #CancelDate(CancelDate)
SELECT EquipmentDetails.value('.','varchar(500)') as CancelDate
FROM @doc.nodes('/Equipment/EquipmentRecord/CancelDate/.') Equipment(EquipmentDetails)

SELECT EquipmentType,MfgName,Model,ActDate,CancelDate 
FROM #EquipmentType ET
Inner Join #MfgName MN ON (ET.ID = MN.ID)
Inner Join #Model MO ON (MN.ID = MO.ID)
Inner Join #ActDate AD ON (MO.ID = AD.ID)
Inner Join #CancelDate CD ON (AD.ID = CD.ID)

DROP TABLE #EquipmentType
DROP TABLE #MfgName
DROP TABLE #Model
DROP TABLE #ActDate
DROP TABLE #CancelDate

but I want a simple way to do this?

但我想要一个简单的方法?

2 个解决方案

#1

Use OPENXML; be sure to remove the document reference (sp_xml_removedocument) to avoid memory leaks:

使用OPENXML;请务必删除文档引用(sp_xml_removedocument)，以避免内存泄漏:

DECLARE @X XML = '<Equipment>
<EquipmentRecord>
<EquipmentType>E</EquipmentType><MfgName>APPLE iPH564WHT</MfgName><Model>885909600205</Model><ActDate>10/26/2012</ActDate><CancelDate />
</EquipmentRecord>
<EquipmentRecord>
<EquipmentType>E</EquipmentType><MfgName>SAMSUNG D710 HANDSET KIT</MfgName><Model>SPHD710KIT</Model><ActDate>09/04/2012</ActDate><CancelDate>10/01/2012</CancelDate>
</EquipmentRecord>
<EquipmentRecord>
<EquipmentType>E</EquipmentType><MfgName>BLACKBERRY 9670 PURPLE</MfgName><Model>SPRIM9670P</Model><ActDate>08/22/2012</ActDate><CancelDate>08/25/2012</CancelDate>
</EquipmentRecord>
</Equipment>'


DECLARE @iX INT
EXEC sp_xml_preparedocument @ix output, @x


SELECT EquipmentType, MfgName, Model, ActDate, CancelDate = NULLIF(CancelDate, '1900-01-01')
FROM OPENXML(@iX, '//EquipmentRecord', 2) 
WITH (EquipmentType varchar(10),
        MfgName VARCHAR(100),
        Model VARCHAR(100),
        ActDate DATE,
        CancelDate date)

EXEC sp_xml_removedocument @iX

#2

The following query should work:

以下查询应该有效:

declare @X xml = 'your xml';

select  x.r.value('(EquipmentType)[1]', 'varchar(100)') as [EquipmentType],
    x.r.value('(MfgName)[1]', 'varchar(500)') as [MfgName],
    x.r.value('(Model)[1]', 'varchar(500)') as [Model],
    x.r.value('(ActDate)[1]', 'varchar(500)') as [ActDate],
    x.r.value('(CancelDate)[1]', 'varchar(500)') as [CancelDate]
from    @X.nodes('/Equipment/EquipmentRecord') as x(r);

#1