XML is a widely used markup language for storing, transporting, and sharing data. In SQL Server, XML documents can be stored in a database and manipulated using XQuery, a query language specifically designed for XML. In this article, we will explore various techniques to find and replace XML data in SQL Server using XQuery.

Replacing an Entire XML Document

Previously, developers would replace the entire XML document in a table column when updating XML information. However, in the latest versions of SQL Server, the XML modify() function in XQuery can be used to update XML data type columns. Here’s an example of replacing an entire XML document:

UPDATE user_details
SET xml_data = '<user>
  <user_id>1</user_id>
  <fname>Josef</fname>
  <lname>Brown</lname>
  <email_>jo0@adventure-works.com</email_>
</user>'
WHERE user_id = 1

Replacing a Key Value in the XML Column

To replace a specific key value in the XML column, you can use the REPLACE function along with casting the XML data type column to NVARCHAR(MAX). Here’s an example:

UPDATE user_details
SET xml_data = REPLACE(CAST(xml_data AS NVARCHAR(MAX)), '<fname>Jo</fname>', '<fname>Josef</fname>')
WHERE user_id = 1

Inserting, Replacing, and Deleting XML Tags

The modify() function in XQuery allows us to perform insert, update, and delete operations on XML data type columns. Here are some examples:

Inserting XML Tags

The INSERT command within the modify() function allows us to add a new XML tag to a specified XML path location. The XML path can be conditional, meaning it can exist with multiple parent tags. Here’s an example:

UPDATE user_details
SET xml_data.modify('insert <gender>Male</gender> into (/user)[1]')
WHERE user_id = 2

Replacing XML Tags

The REPLACE parameter within the modify() function allows us to modify the value of a specific XML tag in the XML data type column. The XML path can be conditional, meaning it is possible to have the same tags multiple times on the derived path in the XML. Here’s an example:

UPDATE user_details
SET xml_data.modify('replace value of (/user/fname/text()) with "Josef"')
WHERE user_id = 1

Deleting XML Tags

The DELETE parameter within the modify() function allows us to delete XML tags in an XML column or variable. Here’s an example:

UPDATE user_details
SET xml_data.modify('delete (/user/gender)')
WHERE user_id = 2

These are just a few examples of how XQuery can be used to manipulate XML data in SQL Server. The modify() function provides a powerful tool for updating XML documents stored in XML data type columns. By leveraging XQuery, developers can efficiently query and update XML data in their SQL Server databases.