SQL Server - View XML-Data as Table

Question

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

Returns