Converting multiple nodes to a recordset

• Anders Pedersen

  SSChampion

  Points: 11410

  More actions

  March 8, 2007 at 3:18 pm

  #76670

  SQL 2005

  I have an XML datatype passed into a stored procedure, part of this XML has a changelog that can vary in length.  I need to convert this to records.  I.e. I am inserting it into an audit table.  I can't seem to get this to work since if you are using a .value you can only get one record back.  The below shows what I have in the XML variable (the total XML is about 190K so only took out the relevant parts).  The last 3 select statements shows how I need the data to look at, but for both records.

  Thanks.

  declare

  @xml_data xml

  declare

  @T table (C1 XML)

  select

  @xml_data = '<processforms>

  <ArrayOfChangeItem>

  - <ChangeItem>

  <ItemPath>NewBusiness._Application._Owners[0]._FirstName</ItemPath>

  <OrigValue>Anders</OrigValue>

  <NewValue>tester</NewValue>

  </ChangeItem>

  <ChangeItem>

  <ItemPath>NewBusiness._Application._Owners[0]._LastName</ItemPath>

  <OrigValue>Anders</OrigValue>

  <NewValue>tester</NewValue>

  </ChangeItem>

  </ArrayOfChangeItem>

  </processforms>'

  select

  @xml_data.value('(/processforms/ArrayOfChangeItem/ChangeItem/ItemPath)[2]', 'varchar(255)'),

  @xml_data.value('(/processforms/ArrayOfChangeItem/ChangeItem/OrigValue)[2]', 'varchar(255)'),

  @xml_data.value('(/processforms/ArrayOfChangeItem/ChangeItem/NewValue)[2]', 'varchar(255)')

• Anders Pedersen

  SSChampion

  Points: 11410

  More actions

  March 9, 2007 at 6:46 am

  #694074

  So I found two solutions, well I found one that is not so good, then one of my co workers showed me what is most likely the correct way.....

  My solution:

  insert

  into @T

  select

  T.c.query('ItemPath') ,

  T.c.query(

  'OrigValue'),

  T.c.query(

  'NewValue')

  from

  @xml_data.nodes('/processforms/ArrayOfChangeItem/ChangeItem') T(c)

  --select * from @T

  select

  ItemPath.value('(/ItemPath)[1]', 'varchar(255)'),

  OrigValue.value(

  '/OrigValue[1]', 'varchar(255)'),

  NewValue.value(

  '/NewValue[1]', 'varchar(255)')

  from

  @T

   

  --***********************************

  His solution

  select

  xml_req.value('./ItemPath[1]','varchar(255)') as ItemPath,

  xml_req.value('./OrigValue[1]', 'varchar(255)') as OrigValue,

  xml_req.value('./NewValue[1]', 'varchar(255)') as OrigValue

  from

  @xml_data.nodes('//ChangeItem') as details(xml_req)