How to get info from an xml stored in a table column into different column

• LeeLuv

  Mr or Mrs. 500

  Points: 588

  More actions

  December 9, 2009 at 6:00 am

  #385913

  '<Root>

  <LineItem>

  <LineItemReference>30048</LineItemReference>

  </LineItem>

  <LineItem>

  <LineItemReference>30049</LineItemReference>

  </LineItem>

  </Root>'

  Have this xml stored in a column of a table and I need to take this data and store it in a different table it must be like this

  LineItemReference

  30048

  30049

  I need to do a bulk insert and don’t want to use a loop plz help

• Tommy Bollhofer

  SSChampion

  Points: 14940

  More actions

  December 9, 2009 at 11:18 am

  #1090070

  If I understand your question correctly, you are looking for something like:

  declare @xml xml

  set @xml = N'

  <Root>

  <LineItem>

  <LineItemReference>30048</LineItemReference>

  </LineItem>

  <LineItem>

  <LineItemReference>30049</LineItemReference>

  </LineItem>

  </Root>'

  select LineItems.LineItem.value(

  '.'

  ,'int'

  ) as LineItemValue

  from @xml.nodes(

  '//LineItem/LineItemReference'

  ) LineItems (LineItem)

  Some more information on XQuery and XML-DML -

  http://www.15seconds.com/Issue/050803.htm

  Tommy

  Follow @sqlscribe

• LeeLuv

  Mr or Mrs. 500

  Points: 588

  More actions

  December 9, 2009 at 11:00 pm

  #1090354

  hello

  what i need to do is select this xml from the column in a table where it is stored and insert the values form that xml into another table

• LeeLuv

  Mr or Mrs. 500

  Points: 588

  More actions

  December 9, 2009 at 11:20 pm

  #1090361

  sorry about the post before this is what i need to do plz help

  i have a table that have info stored like this :

  ID lineXML

  3 <Root><LineItem><LineItemReference>30048</LineItemReference></LineItem><LineItem><LineItemReference>30049</LineItemReference></LineItem></Root>

  4 <Root><LineItem><LineItemReference>30048</LineItemReference></LineItem><LineItem><LineItemReference>30049</LineItemReference></LineItem></Root>

  i need to take this info and store it in a different table and it must be displayed like this:

  ID line

  3 30048

  3 30049

  4 30048

  4 30049

• Tommy Bollhofer

  SSChampion

  Points: 14940

  More actions

  December 10, 2009 at 8:00 am

  #1090573

  are your looking for something like -

  declare @XmlTest table

  ( ID int identity(1,1), LineXml xml)

  insert into @XmlTest (LineXml)

  values

  ('

  <Root>

  <LineItem>

  <LineItemReference>30044</LineItemReference>

  </LineItem>

  <LineItem>

  <LineItemReference>30045</LineItemReference>

  </LineItem>

  </Root>'),

  ('

  <Root>

  <LineItem>

  <LineItemReference>30046</LineItemReference>

  </LineItem>

  <LineItem>

  <LineItemReference>30047</LineItemReference>

  </LineItem>

  </Root>'),

  ('

  <Root>

  <LineItem>

  <LineItemReference>30048</LineItemReference>

  </LineItem>

  <LineItem>

  <LineItemReference>30049</LineItemReference>

  </LineItem>

  </Root>')

  select ID,LineXml.value('(/Root/LineItem)[1]', 'int') as Line

  from @XmlTest

  union

  select ID,LineXml.value('(/Root/LineItem)[2]', 'int') as Line

  from @XmlTest

  Tommy

  Follow @sqlscribe