如何在SQL Server中将XML数据转换为行列数据 [英] How to convert XML data into row column data in SQL Server

问题描述

我有一个在数据库中有XML数据的要求，我需要从数据库中提取行和列的形式.我的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

<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>

我希望像

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

在SQL Server中有什么简单的方法吗?

is there any simple way to this in SQL server?

我尝试过并且奏效了

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?

推荐答案

使用OPENXML；确保删除文档引用(sp_xml_removedocument)，以避免内存泄漏:

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

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

这篇关于如何在SQL Server中将XML数据转换为行列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！