January 25, 2021
Hot Topics:

Working with the XML Data Type of SQL Server

  • By Bipin Joshi
  • Send Email »
  • More Articles »

In the example provided in Figure 4, you used the XML data type for the @xmldata variable. What if you receive the XML fragment as a plain string (VARCHAR)? In such cases, it would be wise to type cast the string into an XML data type by using the CONVERT() function. Figure 5 will make this approach clear.

Figure 5: Using the CONVERT() function to convert a string to an XML data type

Carefully observe the use of the CONVERT() function. The CONVERT() function accepts three parameters. The first parameter indicates the target data type (XML in your case). The second parameter indicates the source data (@plainstring variable in your case). Finally, the third parameter indicates that the insignificant white spaces will be discarded from the XML fragment. Note that using XML data type is recommended over using the VARCHAR type because the XML data type checks for well formedness of the XML fragment.

Modifying Parts of the XML Data

In the above examples we inserted or updated an entire record in the EmployeesAsXml table. What if we wish to modify a part of the XML fragment already stored? Luckily, the XML data type provides what is known as XML Data Modification Language (XML DML). XMl DML statements allow you to insert, update or delete data from the XML fragment stored in the xml column.

Now, say you want to insert another <employee> element in addition to what you originally inserted (with employee ID 1). To accomplish this, you will need to execute the following XML DML statement:

Figure 6: Inserting nodes in existing the XML data

Observe the above UPDATE statement carefully. It invokes the modify() method of the XML data type. The modify() method accepts a string that instructs whether the data is to be inserted, updated, or deleted followed by the actual data. The above example inserts a new <employee> node after the first employee node. Notice the insert and after clauses closely. The insert clause itself appears inside the string value followed by an <employee> node to be inserted. The after clause specifies the location where the new node is to be inserted. Just like the after clause you can also use before, as first and as last clauses.

Now, assume that you want to substitute the employeeid attribute of an <employee> element with a value of 100. To do this, you need to issue the statement shown in Figure 7.

Figure 7: Updating existing XML data

Here, you used a replace value of a statement that specifies the markup to be replaced (employeeid attribute in your case) and the new value that is to be substituted (100 in your case). You set the employeeid attribute of the first <employee> element to 100 in the example above.

To delete an <employee> node, you will need to use the delete statement as shown in Figure 8:

Figure 8: Deleting existing XML content

Here, the delete statement specifies that the <employee> node with employeeid value of 1 be deleted from the XML fragment.

As you might have noticed by now, the XML DML heavily uses XQuery syntax. XQuery is a W3C proposed standard that deals with XML queries. The syntax of XQuery is based on XPath syntax.

Page 2 of 4

This article was originally published on June 25, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date