XQuery is a query language for XML. XML is a markup language that provides software and hardware-independent way of storing, transporting, and sharing data. In this case, XML documents are stored in a SQL Server database. XQuery is used to manipulate, find and extract information stored in the XML format. It’s analogous to SQL for databases.
In this article, we’ll introduce different ways to find and replace XML data using SQL Server XQuery.
Previously, a developer would update XML information by replacing the full XML document in the table column where it resided. But in the latest versions of SQL Server, we can use the XML modify() function in XQuery to update the XML data type column. We’ll look at both the old and new ways to query and update the data.
Refer to the sample table below, called user_details. It has an XML data type column labeled xml_data, and each row has an XML document containing detailed information. For user_id = 1, the value of the fname key is “Jo.” We will update the same row using different methods.
Replace XML document
Replacing an entire XML document is required in SQL Server when a specific key-set isn’t updating. Here, a full XML document is supplanted by a new one. This generally occurs when a complete XML document is changed by a customer application.
In this example, there is a full XML replacement with an update statement:
|
1 2 3 4 5 6 7 8 |
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 |
Replace key value in the XML column
To replace a key value in the XML column, a developer would have replaced the XML for the specific key-set:
|
1 2 3 |
UPDATE user_details SET xml_data = REPLACE(CAST(xml_data AS NVARCHAR(MAX)), '<fname>Jo</fname>', '<fname>Josef</fname>') WHERE user_id = 1 |
Note that the replace function is applied with casting on the XML data type column to avoid a SQL Server error, as shown below:
|
1 2 3 |
UPDATE user_details SET xml_data = REPLACE(xml_data, '<fname>Jo</fname>', '<fname>Josef</fname>') WHERE user_id = 1 |
Msg 8116, Level 16, State 1, Line 14
Argument data type xml is invalid for argument 1 of replace function.
Replace with an update statement replaces all XML tags in the XML document that can cause an issue. When the client needs to replace a specific XML element or attribute value, XQuery is a good solution for targeting the particular element or attribute.
Insert, replace or delete XML tag – value set in XML data type column or XML document using XQuery
The modify() function in XQuery allows us to change a value in the XML document stored in the XML data type column. In the traditional model, we retrieve the XML, update the details and replace it. The modify() is the DML based function with an argument of XPATH to perform insert, update or delete the elements or attributes with the XML based column in the table. Now, we’ll look at some alternative approaches.
Insert XML tag
Within the modify() function, Insert allows us to add a new XML tag to a specified XML path location. This XQuery command needs two parameters a new XML tag with the value that will be inserted; and the XML Path where the new XML tag will be published:
|
1 |
xml.modify(insert "new_tag" into "xml_path(Can be Conditional)") |
XML path can be conditional because the path can exist with multiple parent tags. So, the condition can bifurcate to be inserted within the proper address in the XQuery. For example:
|
1 2 3 |
UPDATE user_details SET xml_data.modify('insert <gender>Male</gender> into (/user)[1]') WHERE user_id = 2 |
With the same path, there can be a multiple-element in the XML. To perform the update or delete action on the specific target XML element, XQuery should be defined with the element id with [1], [2]… or apply a condition on an attribute if it exists.
Replace XML tag
Within the modify() function, the Replace parameter allows us to modify the value of a specific XML-tag in the XML data type column or variable. XQuery needs an XML path and a new value with which to update an existing value in the XML tag:
|
1 |
xml.modify(replace value of "xml_path" with "new_value") |
Here, the XML path can be conditional because it is possible to have the same tags multiple times on the derived path in the XML. If multiple tags exist and the user does not validate the condition to update them, the tags can be updated by the query. See the example below:
|
1 2 3 |
UPDATE user_details SET xml_data.modify('replace value of (/user/fname/text()) with "Josef"') WHERE user_id = 1 |
How can the target XML path be conditional?
In an XML document, different tags can exist with a similar name on the target XML path mentioned in the modify() function. So, the modify() function will carry out the activity on an inappropriate XML tag which is situated in the same position in the target XML path:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<users> <user user_id="1"> <fname>Josef</fname> <lname>Brown</lname> <email_>jo0@adventure-works.com</email_> <gender>Male</gender> </user> <user user_id="2"> <fname>Katie</fname> <lname>McAskill-White</lname> <email_>katie0@adventure-works.com</email_> </user> </users> |
For example, consider XML that has multiple tags with the same name in the XML tag <users>. When we execute the above statement without a condition, then the first fname will be updated with a new value on the targeted XML path. But when a user wants to modify a specific element of those, then there must be a condition to filter the element. The filter can be applied by adding a condition on attribute or element with a specific value, as shown below.
|
1 |
xml.modify('replace value of (/users/user[@user_id=("2")]/fname/text()) with "Kat"') |
Here, filter applied by <user user_id=”2″> with adding [@user_id=(“2”)] in the XML path. Even there can be multiple filters with different tags in the XML.
Delete XML tag
Within the modify() function, the Delete parameter allows us to delete XML-tags in an XML column or XML variable. A query needs the XML path to delete the target XML tags. See the example:
|
1 2 3 |
UPDATE user_details SET xml_data.modify('delete (/user/gender)') WHERE user_id = 2 |
This article describes the feature of XQuery and related implicit functions with the data model. We’ve explained different ways to update XML in SQL Server and the modify() function support in XQuery.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020