Modify XML stored in Microsoft SQL Server using SQL and XPath

Sometimes there is necessary to store XML documents in a SQL database and then there might be needs to modify an element in that document. The good thing is that it is possible to do all this directly using SQL in Microsoft SQL Server.

DECALRE @T TABLE(Id uniqueidentifier, doc xml)
;WITH XMLNAMESPACES ('http://namespace' AS ns), xmlfile AS (
  SELECT CAST(xmldoc AS xml) xmldoc
  FROM DocumentTable
), base AS (
  SELECT xmldoc,
         xmldoc.value('(/ns:Root/ns:Head/ns:Id)[1], 'uniqueidentifier') Id
  FROM xmlfile
  WHERE xmldoc.value('(/ns:Root/ns:Head/ns:Type)[1], 'nvarchar(100)')='TestType'
)
INSERT INTO @T(Id, doc)
SELECT Id, xmldoc
FROM base

;WITH XMLNAMESPACES ('http://namespace' AS ns)
UPDATE @T
SET doc.modify('replace value of (/ns:Root/ns:Head/ns:Type/text())[1] with "NewTestType" ')

UPDATE D
SET D.xmldoc=CAST(TMP.doc AS NVARCHAR(MAX))
FROM DocumentTable D
INNER JOIN @T TMP ON T.Id=D.Id

In my case when I needed to modify an element in the XML stored in a table, the datatype of the the xml document column was nvarchar(max), that is the reason I cast the values. First to the xml datatype and then back to nvarchar(max) when I need to update the value with the new xml file.

The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content
1) modify('replace value of (/ns:Root/ns:Head/ns:Type)[1] with "NewTestType"')
2) modify('replace value of (/ns:Root/ns:Head/ns:Type/text())[1] with "NewTestType"')

I got in the beginning when I was using modify, the error message above. My modify statement was like the number 1 in the example above. What I needed to do was specify text()in the xpath.

Conclusion

Modifying xml directly using SQL is in some cases a nice way to fix a bug or error happening. The SQL is definatly not very good performance vice, but it works.

Teis Lindemark

Read more posts by this author.