asked on

Some TSQL

I have an xml document that is passed into a stored procedure as a parameter called @xmldata.

It can look like this. Basically a name/value pair.

<BRUM-registret>
  <TestForm1>
    <DocumentId>6</DocumentId>
    <text1>Hello.</text1>
    <text2>Goodbye</text2>
    <text3>Page 2.</text3>
  </TestForm1>
</BRUM-registret>

Open in new window

I need to traverse the document and basically update a table that holds the name/value pairs

So in the case above I need three updates to do the following:

Update MyTable set FieldValue = ‘Hello’ where DocumentId=6 and FieldName = ‘text1’
Update MyTable set FieldValue = ‘Goodbye’ where DocumentId=6 and FieldName = ‘text2’
Update MyTable set FieldValue = ‘Page 2.’ where DocumentId=6 and FieldName = ‘text3’

You can see the DocumentId is a “special name/value pair” because it is used as part of the key to update the other records.

Can anyone suggest some generic code that will parse the XML, extract the values, and update the table?

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

I would say this is the starter:

declare @xml xml
set @xml = '<BRUM-registret>
  <TestForm1>
    <DocumentId>6</DocumentId>
    <text1>Hello.</text1>
    <text2>Goodbye</text2>
    <text3>Page 2.</text3>
  </TestForm1>
</BRUM-registret>'

select n.f.query('fn:local-name(.)') name
 , n.f.value('.', 'nvarchar(max)') value
 , n.f.query('.') xml
  from ( select @xml data ) t
  cross apply t.data.nodes('//TestForm1/*') as n(f)

Open in new window

which returns this:

name	value	xml
DocumentId	6	<DocumentId>6</DocumentId>
text1	Hello.	<text1>Hello.</text1>
text2	Goodbye	<text2>Goodbye</text2>
text3	Page 2.	<text3>Page 2.</text3>

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial