January 16, 2021
Hot Topics:

Working with the XML Data Type of SQL Server

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

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

The query() method accepts an XQuery expression on the basis of which data is to be filtered and returns the results as XML. The following example (see Figure 9) fetches an employee whose employeeid attribute is 1.

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.

Click here for a larger image.

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

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