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