Working with the XML Data Type of SQL Server
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