inserting the xml elements into existing table

• rams.prsk

  Ten Centuries

  Points: 1241

  More actions

  December 29, 2011 at 2:55 am

  #96690

  Hi All,

  Iam having the xml with the data as shown below,

  declare @x xml

  set @x = '<SinterklaasWishlists>

  <child>

  <name>Tim</name>

  <wishlist>

  <article>

  <artno>21491269</artno>

  <description>Crane</description>

  <price>12.50</price>

  </article>

  <article>

  <artno>21499517</artno>

  <description>Keyboard</description>

  <price>10</price>

  </article>

  <article>

  <artno>21521591</artno>

  <description>Crime Investigation Game</description>

  <price>9.95</price>

  </article>

  </wishlist>

  </child>

  <child>

  <name>Tim2</name>

  <wishlist>

  <article>

  <artno>3145678</artno>

  <description>Mouse</description>

  <price>12.50</price>

  </article>

  </wishlist>

  </child>

  </SinterklaasWishlists>'

  I want to extract the elements of xml using sql query and insert the data into the table as shown below:

  CHILDNAME ARTICLE_NUMBER DESCRIPTION PRICE

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

  Tim1 21491269 Crane 12.50

  Tim1 21499517 Keyboard 10

  Tim1 21521591 Crime Investigation Game 9.95

  Tim2 3145678 Mouse 12.50

  Kindly help me on the same

• Arthur Olcot

  SSCertifiable

  Points: 6564

  More actions

  January 5, 2012 at 7:14 am

  #1428987

  This is one way to shred the example xml into the format ready for you to insert into a table:

  select c.value('../../name[1]', 'varchar(100)'),

  c.value('artno[1]', 'varchar(100)'),

  c.value('description[1]', 'varchar(100)'),

  c.value('price[1]', 'money')

  from @x.nodes('//article') t(c)

  Hope this gets you onto the right track.

• rams.prsk

  Ten Centuries

  Points: 1241

  More actions

  January 5, 2012 at 9:33 am

  #1429121

  thanks a lot dude 🙂 it worked like a charm 2 me 🙂

• Arthur Olcot

  SSCertifiable

  Points: 6564

  More actions

  January 5, 2012 at 9:36 am

  #1429126

  No Problem. thanks for the feedback.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 5, 2012 at 1:43 pm

  #1429343

  I'd rather use the CROSS APPLY approach to avoid "climbing up" two levels for each article node to get the name element. Performance impact can be significant depending on the xml size and node structure.

  SELECT

  c.value('name[1]', 'varchar(100)'),

  v.value('artno[1]', 'varchar(100)'),

  v.value('description[1]', 'varchar(100)'),

  v.value('price[1]', 'money')

  FROM @x.nodes('SinterklaasWishlists/child') t(c)

  CROSS APPLY T.c.nodes('wishlist/article')u(v)

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]