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.
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?