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/