我有一个要求,数据库中有 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>
我希望像这样提取数据
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 有什么简单的方法吗?
我尝试过并且成功了
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
但我想要一个简单的方法来做到这一点?