Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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')
- SELECT TestValue1,
- TestValue2,
- TestValue3,
- TestValue4,
- TestValue5,
- TestValue6,
- TestValue7
- FROM (
- SELECT dd.DAT_Detail_ID,
- CAST(t.c.query('local-name(.)') as nvarchar(max)) as [Columns],
- t.c.value('.','nvarchar(max)') as [Values]
- FROM DAT_Detail dd
- CROSS APPLY XMLDetaildata.nodes('/NewDataSet/DetailTest_A10/*') as t(c)
- WHERE dd.DAT_Detail_ID IN (
- '9B30DDAF-0733-4D0D-BCBD-54DA3B56C8F9',
- '50041AE4-BE73-4281-A36E-7448F6F35E03')
- ) t
- PIVOT (
- MAX([Values]) FOR [Columns] IN (TestValue1,TestValue2,TestValue3,TestValue4,TestValue5,TestValue6,TestValue7)
- ) as pvt
- TestValue1 TestValue2 TestValue3 TestValue4 TestValue5 TestValue6 TestValue7
- 4321 442 453 4645 4123 414 4253
- 2321 242 253 2645 2123 214 2253
- SELECT t.c.value('(*)[1]','nvarchar(max)') as TestValue1,
- t.c.value('(*)[2]','nvarchar(max)') as TestValue2,
- t.c.value('(*)[3]','nvarchar(max)') as TestValue3,
- t.c.value('(*)[4]','nvarchar(max)') as TestValue4,
- t.c.value('(*)[5]','nvarchar(max)') as TestValue5,
- t.c.value('(*)[6]','nvarchar(max)') as TestValue6,
- t.c.value('(*)[7]','nvarchar(max)') as TestValue7
- FROM DAT_Detail dd
- CROSS APPLY XMLDetaildata.nodes('/NewDataSet/DetailTest_A10') as t(c)
- WHERE dd.DAT_Detail_ID IN (
- '9B30DDAF-0733-4D0D-BCBD-54DA3B56C8F9',
- '50041AE4-BE73-4281-A36E-7448F6F35E03')
- SELECT n.x.value('TestValue1[1]', 'INT') as TestValue1, n.x.value('TestValue2[1]', 'INT') as TestValue2 --, ...
- FROM DAT_Detail
- CROSS APPLY XMLDetaildata.nodes('/NewDataSet/DetailTest_A10') n(x)
- WHERE DAT_Detail_ID IN ('9B30DDAF-0733-4D0D-BCBD-54DA3B56C8F9', '50041AE4-BE73-4281-A36E-7448F6F35E03')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement