SQL Server 2008 to read XML data to relational table

• jason123

  SSC-Addicted

  Points: 455

  More actions

  May 31, 2012 at 7:33 pm

  #261945

  Dear everyone,

  Could you please help me to use write SQL to transform XML data to relational table?

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

  <c1>CUSDS</c1>

  <c2>Davit Seng</c2>

  <c3>Davit Seng</c3>

  <c5>47, St 172, Daun Penh</c5>

  <c6>Chhey Chumnas</c6>

  <c7>Phnom Penh,</c7>

  <c8>Central Post office P.O Box 479</c8>

  <c8 m="2">855</c8>

  <c8 m="3">955</c8>

  <c9>CAMBODIA</c9>

  <c9 m="2">USA</c9>

  <c13 m="1" s="5" />

  <c14 m="1" s="5" />

  <c15>TEST1</c15>

  <c15 m="1" s="2">TEST2</c15>

  <c15 m="1" s="3">TEST3</c15>

  <c15 m="1" s="4">TEST4</c15>

  <c15 m="1" s="5">TEST5</c15>

  <c16>TEST1.1</c16>

  <c16 m="1" s="2">TEST2.2</c16>

  <c16 m="1" s="3">TEST3.3</c16>

  <c16 m="1" s="4">TEST4.4</c16>

  <c16 m="1" s="5" />

  <c23>3001</c23>

  <c24>1</c24>

  <c26>5000</c26>

  <c27>30</c27>

  <c28>KH</c28>

  <c29>22</c29>

  <c30>KH</c30>

  <c31>20050222</c31>

  <c44>999999</c44>

  <c45>1</c45>

  <c48>KH0010001</c48>

  <c49>NO</c49>

  <c52>NO</c52>

  <c132>NULL</c132>

  <c133>NULL</c133>

  <c136>NULL</c136>

  <c137>NULL</c137>

  <c178 m="19">Y</c178>

  <c178 m="31">1000</c178>

  <c178 m="31" s="2" />

  <c178 m="32" s="2" />

  <c178 m="33" s="2" />

  <c178 m="34" s="2" />

  <c178 m="35">20111206</c178>

  <c178 m="172" />

  <c181>6</c181>

  <c182>231_VFC.03__OFS_BROWSERTC</c182>

  <c183>1205302357</c183>

  <c184>225_VFC.01_OFS_BROWSERTC</c184>

  <c185>KH0010001</c185>

  <c186>1</c186>

  </row>

  Thank for your help.

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  June 1, 2012 at 2:17 am

  #1495708

  There's way too much there for me to type it all out, so I'll give you the general idea and you can take it from there.

  First, let's put your XML in a variable: -

  DECLARE @XML AS XML;

  SET @XML = CAST('<row id="100112" xml:space="preserve">

  <c1>CUSDS</c1>

  <c2>Davit Seng</c2>

  <c3>Davit Seng</c3>

  <c5>47, St 172, Daun Penh</c5>

  <c6>Chhey Chumnas</c6>

  <c7>Phnom Penh,</c7>

  <c8>Central Post office P.O Box 479</c8>

  <c8 m="2">855</c8>

  <c8 m="3">955</c8>

  <c9>CAMBODIA</c9>

  <c9 m="2">USA</c9>

  <c13 m="1" s="5" />

  <c14 m="1" s="5" />

  <c15>TEST1</c15>

  <c15 m="1" s="2">TEST2</c15>

  <c15 m="1" s="3">TEST3</c15>

  <c15 m="1" s="4">TEST4</c15>

  <c15 m="1" s="5">TEST5</c15>

  <c16>TEST1.1</c16>

  <c16 m="1" s="2">TEST2.2</c16>

  <c16 m="1" s="3">TEST3.3</c16>

  <c16 m="1" s="4">TEST4.4</c16>

  <c16 m="1" s="5" />

  <c23>3001</c23>

  <c24>1</c24>

  <c26>5000</c26>

  <c27>30</c27>

  <c28>KH</c28>

  <c29>22</c29>

  <c30>KH</c30>

  <c31>20050222</c31>

  <c44>999999</c44>

  <c45>1</c45>

  <c48>KH0010001</c48>

  <c49>NO</c49>

  <c52>NO</c52>

  <c132>NULL</c132>

  <c133>NULL</c133>

  <c136>NULL</c136>

  <c137>NULL</c137>

  <c178 m="19">Y</c178>

  <c178 m="31">1000</c178>

  <c178 m="31" s="2" />

  <c178 m="32" s="2" />

  <c178 m="33" s="2" />

  <c178 m="34" s="2" />

  <c178 m="35">20111206</c178>

  <c178 m="172" />

  <c181>6</c181>

  <c182>231_VFC.03__OFS_BROWSERTC</c182>

  <c183>1205302357</c183>

  <c184>225_VFC.01_OFS_BROWSERTC</c184>

  <c185>KH0010001</c185>

  <c186>1</c186>

  </row>' AS XML);

  OK, now let's convert some of that into a "table".

  SELECT

  xmlData.Col.value('@id','INT'),

  xmlData.Col.value('c1[1]','VARCHAR(10)'),

  xmlData.Col.value('c2[1]','VARCHAR(50)'),

  xmlData.Col.value('c3[1]','VARCHAR(50)'),

  xmlData.Col.value('c5[1]','VARCHAR(100)'),

  xmlData.Col.value('c6[1]','VARCHAR(100)'),

  xmlData.Col.value('c7[1]','VARCHAR(100)'),

  xmlData.Col.value('c8[1]','VARCHAR(100)'),

  xmlData.Col.value('c8[2]','VARCHAR(100)'),

  xmlData.Col.value('c8[2]/@m','INT'),

  xmlData.Col.value('c8[3]','VARCHAR(100)'),

  xmlData.Col.value('c8[3]/@m','INT'),

  xmlData.Col.value('c9[1]','VARCHAR(10)'),

  xmlData.Col.value('c9[2]','VARCHAR(10)'),

  xmlData.Col.value('c9[2]/@m','INT'),

  xmlData.Col.value('c13[1]/@m','INT'),

  xmlData.Col.value('c13[1]/@s','INT'),

  xmlData.Col.value('c14[1]/@m','INT'),

  xmlData.Col.value('c14[1]/@s','INT'),

  xmlData.Col.value('c15[1]','VARCHAR(10)'),

  xmlData.Col.value('c15[1]/@m','INT'),

  xmlData.Col.value('c15[1]/@s','INT'),

  xmlData.Col.value('c15[2]','VARCHAR(10)'),

  xmlData.Col.value('c15[2]/@m','INT'),

  xmlData.Col.value('c15[2]/@s','INT'),

  xmlData.Col.value('c15[3]','VARCHAR(10)'),

  xmlData.Col.value('c15[3]/@m','INT'),

  xmlData.Col.value('c15[3]/@s','INT'),

  xmlData.Col.value('c15[4]','VARCHAR(10)'),

  xmlData.Col.value('c15[4]/@m','INT'),

  xmlData.Col.value('c15[4]/@s','INT'),

  xmlData.Col.value('c15[5]','VARCHAR(10)'),

  xmlData.Col.value('c15[5]/@m','INT'),

  xmlData.Col.value('c15[5]/@s','INT')

  FROM @XML.nodes('//row') xmlData(Col);

  And that returns: -

  ----------- ---------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ---------- ---------- ----------- ----------- ----------- ----------- ----------- ---------- ----------- ----------- ---------- ----------- ----------- ---------- ----------- ----------- ---------- ----------- ----------- ---------- ----------- -----------

  100112 CUSDS Davit Seng Davit Seng 47, St 172, Daun Penh Chhey Chumnas Phnom Penh, Central Post office P.O Box 479 855 2 955 3 CAMBODIA USA 2 1 5 1 5 TEST1 NULL NULL TEST2 1 2 TEST3 1 3 TEST4 1 4 TEST5 1 5

  Do you understand how to finish it off?

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---