Scherbe Scherbe - 1 month ago 7
SQL Question

SQL Server - View XML-Data as Table

i hope anyone can help me with my problem...

I have a Table (DAT_Detail) in SQL Server 2008R2 with a XML-Column. In the XML-Column are my detailinformations for some tests... (i cannot change the table to another design, it hast to stay as xml)
My table has 2 columns: DAT_Detail_ID (uniqueidentifier) and XMLDetaildata (XML).

This is the script to add my testdata:

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>');


What i want is to select 2 or more rows and convert the xml to a table-format to show the values in a report in a SQLReportServer.

I have a query for translate xml to table but i can only use 1 row for this. As soon as i try to take more rows, only the last row is translated to a table.
My Script:

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


What i get is a table:

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


I tried to change my script to:

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


to get this:

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


but it failed... has anyone another idea to get me in the right direction?

Answer
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')

Returns

enter image description here