Read nodes from XML to table

• jason123

  SSC-Addicted

  Points: 455

  More actions

  September 30, 2012 at 3:16 am

  #268325

  Hi everyone,

  I have xml structure as below:

  <row id="PDPD1134200001" xml:space="preserve">

  <c2>100113</c2>

  <c3>KHR</c3>

  <c4>1</c4>

  <c5>TR</c5>

  <c6>00</c6>

  <c7>1</c7>

  <c8>21050</c8>

  <c9>B</c9>

  <c10>10049</c10>

  <c11>72.00</c11>

  <c13>0</c13>

  <c14>21050</c14>

  <c15>9700.01</c15>

  <c16>9700.01</c16>

  <c17>-50000</c17>

  <c18>50000</c18>

  <c19>50000</c19>

  <c20>0</c20>

  <c21>IN</c21>

  <c21 m="2">PR</c21>

  <c21 m="3">PE</c21>

  <c21 m="4">PS</c21>

  <c22>0</c22>

  <c22 m="2">50000</c22>

  <c22 m="3">0</c22>

  <c22 m="4">0</c22>

  <c24>20111208</c24>

  <c25>50000</c25>

  <c26>0</c26>

  <c27>100000</c27>

  <c28>IN</c28>

  <c28 m="1" s="2">PR</c28>

  <c28 m="1" s="3">PE</c28>

  <c28 m="1" s="4">PS</c28>

  <c29>10000</c29>

  <c29 m="1" s="2">90000</c29>

  <c29 m="1" s="3">0</c29>

  <c29 m="1" s="4">0</c29>

  <c30>0</c30>

  <c30 m="1" s="2">50000</c30>

  <c30 m="1" s="3">0</c30>

  <c30 m="1" s="4">0</c30>

  <c31>0</c31>

  <c31 m="1" s="2">0</c31>

  <c31 m="1" s="3">0</c31>

  <c31 m="1" s="4">0</c31>

  <c32>10000</c32>

  <c32 m="1" s="2">40000</c32>

  <c32 m="1" s="3">0</c32>

  <c32 m="1" s="4">0</c32>

  <c33>0</c33>

  <c33 m="1" s="2">0</c33>

  <c33 m="1" s="3">0</c33>

  <c33 m="1" s="4">0</c33>

  <c34 m="1" s="4" />

  <c35>GRA</c35>

  <c49>10049</c49>

  <c69>NO</c69>

  <c72>GRA</c72>

  <c78>YES</c78>

  <c81>20111208</c81>

  <c87>0.00</c87>

  <c104>163050535447075.00</c104>

  <c104 m="2">1-2</c104>

  <c105>NO.LINE}NO LINE ALLOCATED{{{{{100113{{100113.0009700.01{</c105>

  <c107>3</c107>

  <c108>11_SOFGEN.10__OFS_BROWSERTC</c108>

  <c109>1208211304</c109>

  <c110>5354_SOFGEN.11</c110>

  <c111>KH0010001</c111>

  <c112>1</c112>

  </row>

  Could you please help give me the clue or completed query to display data as below

  ID PDTYPE (c21) AMOUNT (c22)

  PDPD1134200001 IN 0

  PDPD1134200001 PR 50000

  PDPD1134200001 PS 0

  PDPD1134200001 PE 0

  Note, <c21>IN</c21> <c21 m="2">PR</c21> <c21 m="3">PE</c21> <c21 m="4">PS</c21>, sometimes m of c21 can be more than 20 and <c22> will be variable based on <c21>. For example

  <c21 m=2> PR</c21>

  ............................

  <c21 m=20> PR20</c21>

  Best regards;

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  September 30, 2012 at 4:51 am

  #1543732

  here you go...any questions, just ask.

  DECLARE @xml XML = '

  <row id="PDPD1134200001" xml:space="preserve">

  <c2>100113</c2>

  <c3>KHR</c3>

  <c4>1</c4>

  <c5>TR</c5>

  <c6>00</c6>

  <c7>1</c7>

  <c8>21050</c8>

  <c9>B</c9>

  <c10>10049</c10>

  <c11>72.00</c11>

  <c13>0</c13>

  <c14>21050</c14>

  <c15>9700.01</c15>

  <c16>9700.01</c16>

  <c17>-50000</c17>

  <c18>50000</c18>

  <c19>50000</c19>

  <c20>0</c20>

  <c21>IN</c21>

  <c21 m="2">PR</c21>

  <c21 m="3">PE</c21>

  <c21 m="4">PS</c21>

  <c22>0</c22>

  <c22 m="2">50000</c22>

  <c22 m="3">0</c22>

  <c22 m="4">0</c22>

  <c24>20111208</c24>

  <c25>50000</c25>

  <c26>0</c26>

  <c27>100000</c27>

  <c28>IN</c28>

  <c28 m="1" s="2">PR</c28>

  <c28 m="1" s="3">PE</c28>

  <c28 m="1" s="4">PS</c28>

  <c29>10000</c29>

  <c29 m="1" s="2">90000</c29>

  <c29 m="1" s="3">0</c29>

  <c29 m="1" s="4">0</c29>

  <c30>0</c30>

  <c30 m="1" s="2">50000</c30>

  <c30 m="1" s="3">0</c30>

  <c30 m="1" s="4">0</c30>

  <c31>0</c31>

  <c31 m="1" s="2">0</c31>

  <c31 m="1" s="3">0</c31>

  <c31 m="1" s="4">0</c31>

  <c32>10000</c32>

  <c32 m="1" s="2">40000</c32>

  <c32 m="1" s="3">0</c32>

  <c32 m="1" s="4">0</c32>

  <c33>0</c33>

  <c33 m="1" s="2">0</c33>

  <c33 m="1" s="3">0</c33>

  <c33 m="1" s="4">0</c33>

  <c34 m="1" s="4" />

  <c35>GRA</c35>

  <c49>10049</c49>

  <c69>NO</c69>

  <c72>GRA</c72>

  <c78>YES</c78>

  <c81>20111208</c81>

  <c87>0.00</c87>

  <c104>163050535447075.00</c104>

  <c104 m="2">1-2</c104>

  <c105>NO.LINE}NO LINE ALLOCATED{{{{{100113{{100113.0009700.01{</c105>

  <c107>3</c107>

  <c108>11_SOFGEN.10__OFS_BROWSERTC</c108>

  <c109>1208211304</c109>

  <c110>5354_SOFGEN.11</c110>

  <c111>KH0010001</c111>

  <c112>1</c112>

  </row>'

  SELECT

  n0.nd.value('@id','varchar(50)') AS ID

  ,n1.nd.value('(./text())[1]','varchar(50)') AS PDTYPE

  ,n2.nd.value('(./text())[1]','decimal(12,2)') AS AMOUNT

  FROM

  -- first extract the "row" nodes

  @xml.nodes('row') AS n0(nd)

  CROSS APPLY

  -- then grab all the "c21" nodes

  n0.nd.nodes('c21') AS n1(nd)

  CROSS APPLY

  -- then grab all the "c22" nodes

  n0.nd.nodes('c22') AS n2(nd)

  WHERE

  -- then match the "m" attributes on the c21 nodes to the "m" attributes on the c22 nodes

  COALESCE(n1.nd.value('@m','int'),0) = COALESCE(n2.nd.value('@m','int'),0)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]