我可以帮你进行翻译。以下是需要翻译的内容:
我有一个需求,数据库里有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
有没有一种在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
但我希望有一种简单的方法来实现这个?