sql - 以表格形式查看 XML 数据

Question

我在 SQL Server 2008R2 中有一个带有 XML 列的表 (DAT_Detail)。在 XML-Column 中有一些测试的详细信息......(我不能将表格更改为另一种设计，它必须保留为 XML)。我的表有 2 列:DAT_Detail_ID(唯一标识符)和 XMLDetaildata (XML)。

这是添加我的测试数据的脚本:

INSERT INTO DAT_Detail (DAT_Detail_ID, XMLDetaildata) VALUES ('629E4F85-098D-418B-BF2E-63648DCF60ED', '<NewDataSet><DetailTest_A10><TestValue1>1321</TestValue1><TestValue2>142</TestValue2><TestValue3>153</TestValue3><TestValue4>1645</TestValue4><TestValue5>1123</TestValue5><TestValue6>114</TestValue6><TestValue7>1253</TestValue7></DetailTest_A10></NewDataSet>');
INSERT INTO DAT_Detail (DAT_Detail_ID, XMLDetaildata) VALUES ('9B30DDAF-0733-4D0D-BCBD-54DA3B56C8F9', '<NewDataSet><DetailTest_A10><TestValue1>2321</TestValue1><TestValue2>242</TestValue2><TestValue3>253</TestValue3><TestValue4>2645</TestValue4><TestValue5>2123</TestValue5><TestValue6>214</TestValue6><TestValue7>2253</TestValue7></DetailTest_A10></NewDataSet>');
INSERT INTO DAT_Detail (DAT_Detail_ID, XMLDetaildata) VALUES ('E647B9FB-7B96-440A-ADCB-300F8DEA4BF1', '<NewDataSet><DetailTest_A10><TestValue1>3321</TestValue1><TestValue2>342</TestValue2><TestValue3>353</TestValue3><TestValue4>3645</TestValue4><TestValue5>3123</TestValue5><TestValue6>314</TestValue6><TestValue7>3253</TestValue7></DetailTest_A10></NewDataSet>');
INSERT INTO DAT_Detail (DAT_Detail_ID, XMLDetaildata) VALUES ('50041AE4-BE73-4281-A36E-7448F6F35E03', '<NewDataSet><DetailTest_A10><TestValue1>4321</TestValue1><TestValue2>442</TestValue2><TestValue3>453</TestValue3><TestValue4>4645</TestValue4><TestValue5>4123</TestValue5><TestValue6>414</TestValue6><TestValue7>4253</TestValue7></DetailTest_A10></NewDataSet>');
INSERT INTO DAT_Detail (DAT_Detail_ID, XMLDetaildata) VALUES ('87AE23BA-41DE-4C1E-BA4E-37E7C3419FE3', '<NewDataSet><DetailTest_A10><TestValue1>5321</TestValue1><TestValue2>542</TestValue2><TestValue3>553</TestValue3><TestValue4>5645</TestValue4><TestValue5>5123</TestValue5><TestValue6>514</TestValue6><TestValue7>5253</TestValue7></DetailTest_A10></NewDataSet>');
INSERT INTO DAT_Detail (DAT_Detail_ID, XMLDetaildata) VALUES ('9AAEA106-35C9-40B8-B0D5-7CA7F59E5D90', '<NewDataSet><DetailTest_A10><TestValue1>6321</TestValue1><TestValue2>642</TestValue2><TestValue3>653</TestValue3><TestValue4>6645</TestValue4><TestValue5>6123</TestValue5><TestValue6>614</TestValue6><TestValue7>6253</TestValue7></DetailTest_A10></NewDataSet>');

我想要的是选择 2 行或更多行并将 XML 转换为表格格式，以在 SQLReportServer 中的报告中显示值。

我有一个将 XML 转换为表格的查询，但我只能为此使用 1 行。一旦我尝试获取更多行，只有最后一行被转换为表格。

我的脚本:

DECLARE @XML AS XML
DECLARE @hDoc AS INT

SELECT @XML = XMLDetaildata FROM DAT_Detail WHERE DAT_Detail_ID = '9B30DDAF-0733-4D0D-BCBD-54DA3B56C8F9'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
FROM OPENXML(@hDoc, 'NewDataSet/DetailTest_A10')
WITH 
(
TestValue1 [int] 'TestValue1',
TestValue2 [int] 'TestValue2',
TestValue3 [int] 'TestValue3',
TestValue4 [int] 'TestValue4',
TestValue5 [int] 'TestValue5',
TestValue6 [int] 'TestValue6',
TestValue7 [int] 'TestValue7'
)

EXEC sp_xml_removedocument @hDoc

我得到的是一张 table :

TestValue1  TestValue2  TestValue3  TestValue4  TestValue5  TestValue6  TestValue7
2321        242         253         2645        2123        214         2253

我试图将我的脚本更改为:

SELECT @XML = XMLDetaildata FROM DAT_Detail WHERE DAT_Detail_ID IN ( '9B30DDAF-0733-4D0D-BCBD-54DA3B56C8F9', '50041AE4-BE73-4281-A36E-7448F6F35E03')

得到这个:

TestValue1  TestValue2  TestValue3  TestValue4  TestValue5  TestValue6  TestValue7
2321        242         253         2645        2123        214         2253    
4321        442         453         4645        4123        414         4253

但它失败了...有没有其他想法让我朝着正确的方向前进？

Answer 1

Select B.*
 From  Dat_Detail A
 Cross Apply (
                Select TestValue1 = B.value('TestValue1[1]','int') 
                      ,TestValue2 = B.value('TestValue2[1]','int') 
                      ,TestValue3 = B.value('TestValue3[1]','int') 
                      ,TestValue4 = B.value('TestValue4[1]','int') 
                      ,TestValue5 = B.value('TestValue5[1]','int') 
                      ,TestValue6 = B.value('TestValue6[1]','int') 
                      ,TestValue7 = B.value('TestValue7[1]','int') 
                 From  XMLDetaildata.nodes('/NewDataSet') AS A(Grp)
                 Cross Apply A.Grp.nodes('DetailTest_A10') AS B(B)
             ) B
 Where DAT_Detail_ID  IN ( '9B30DDAF-0733-4D0D-BCBD-54DA3B56C8F9', '50041AE4-BE73-4281-A36E-7448F6F35E03')

返回