Updating SQL XML SQL Server

We have Legacy xml in our SQL server xml that we need to add nodes too. This xml gets published and used by Adobe LiveCycle to produce pdf forms.

We’ve had to modify some of the xml to add new nodes to get new fields to show up, this query will update 1 of the records….

--create XML variable to hold fragment DECLARE @xml AS XML --convert the NTEXT to XML SELECT @xml = CONVERT(XML, form_data) FROM my_table WHERE some_field = 'someData' AND form_data.exist('/some/xquery/path/in/your/xml/yourNewField') = 0 AND form_data.exist('/some/xquery/path/in/your/xml/yourOldField') = 1 AND some_id = 104259

–perform the XML insert

SET @xml.modify(‘insert {(/some/xquery/path/in/your/xml/yourNewField/text())} after (/some/xquery/path/in/your/xml/yourOldField)[1]’)

–replace the previous contents of the NTEXT column with the newly updated XML (converting back to text)

UPDATE my_table

SET form_data = CONVERT(VARCHAR(MAX), @xml)

WHERE some_field = ‘someData’

AND form_data.exist(‘/some/xquery/path/in/your/xml/yourNewField’) = 0

AND form_data.exist(‘/some/xquery/path/in/your/xml/yourOldField’) = 1

AND some_id = 104259

SELECT ecn, form_data

from my_table

WHERE some_field = ‘someData’

AND some_id = 104259

stay tuned on how to update all records in a table with the new values.

 

http://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

Leave a comment