Working with the XML Data Type of SQL Server
Querying XML Data
In the preceding examples, you dealt with data insertion and modification operations. Equally important are query operations. Just like the Modify() method, the XML data type has several methods that allow you to query data. Some of the commonly used methods of the XML data type are listed below:
- query(): Queries the XML data based on XQuery expression and fetches the results of the query
- value(): Queries the XML data and returns a scalar value as a result
- exist(): Indicates whether a given XQuery expression returned any results
Now, you can see the usage of all the above methods.
Using the query() method
Figure 9: Fetching XML elements
Here, you use a SELECT statement along with the query() method. The query() method filters only the <employee> node where the employeeid attribute value is 1. Figure 10 presents the results of executing the above query (formatted for better readability):
Figure 10: Results of the query() method
Using the value() method
The value() method accepts an XQuery expression and returns a scalar value as the result Consult Figure 11.
Figure 11: Fetching single values from XML data
The value() method takes two parameters: the XQuery expression returns a scalar value and data type of the returned scalar value. You specify that the firstname element value of the first node be returned and its data type as varchar(255). If you execute the above query, you will get Nancy as the return value.
Using the exist() method
The exist() method accepts an XQuery expression and returns a value indicating whether any matching node was found. If the matching node is found, it returns 1; otherwise, it returns 0. If the column contains NULL, the value() method also returns NULL. The example shown in Figure 12 checks whether any employee node with the employeeid attribute equal to 1 exists.
Figure 12: Checking if an XML node exists
Attaching an XSD Schema
In preceding examples, your XML markup was not validated against any XSD schema. In many cases, using XSD schema can be advantageous. Some of the advantages include:
- Schemas allow you to detect errors in the XML data early in the life cycle of the application.
- It allows you to validate the new XML data being inserted to the XML column.
- It also can improve the performance of query execution.
To attach a schema to an XML column, first of all you need to create a Schema Collection. This is done as shown in Figure 13:
Figure 13: Adding a schema to schema collection
Page 3 of 4