XQuery in Relational Databases

One of the primary tasks developers are faced with is querying data from some data store and allowing users to view and/or manipulate the information via a Web interface. Typically, the data stores that you query from are traditional relational databases, such as Microsoft SQL Server or Oracle. With relational databases, the de facto means for querying data is SQL. However, with the ever-continuing rise in the popularity of Web services, and the need for a platform-independent, Internet-transferable, data representation format, XML data stores are becoming more and more popular. SQL was never designed for querying semi-structured data stores, and therefore is not suitable for querying XML data stores. So, how do you query an XML data store and retrieve results from such a query? Most developers currently use XSLT and XPath to accomplish this task. However, XPath and XSLT alone are not sufficient for querying the XML data stores, and you need the power of XQuery to be able to maximize the benefits of using XML data stores. Now all the major relational database vendors (including Oracle and Microsoft) support XQuery as part of their database implementations. The following section gives you a quick tour of the XQuery support provided by SQL Server 2005.

XQuery in SQL Server 2005

One of the newly introduced features in SQL Server 2005 is the native XML data type. Using the XML data type, you can create a table that has one or more columns of type XML in addition to relational columns. XML variables and parameters are also allowed. XML values are stored in an internal format as large binary objects (BLOBs) in order to support the XML model characteristics, such as document order and recursive structures, more faithfully.

SQL Server 2005 provides XML schema collections as a way to manage W3C XML Schemas as metadata. An XML data type can be associated with an XML Schema collection to enforce schema constraints on XML instances. When the XML data is associated with an XML Schema collection, it is called typed XML; otherwise it is called untyped XML. Both typed and untyped XML are accommodated within a single framework, the XML data model is preserved, and query processing enforces XML semantics. The underlying relational infrastructure is used extensively for this purpose. It supports interoperability between relational and XML data, thereby making way for more widespread adoption of the XML features.

XML Data Type Query and Data Modification

You can use a T-SQL SELECT statement to retrieve XML instances. Five built-in methods on the XML data type are provided to query and modify XML instances. These methods accept XQuery. The XQuery type system is aligned with that of W3C XML schema types. Most of the SQL types are compatible with the XQuery type system (for example, decimal). A handful of types (for example, xs:duration) are stored in an internal format and suitably interpreted to be compatible with the XQuery type system.

The compilation phase checks static type correctness of XQuery expressions and data modification statements, and uses XML schemas for type inferences in the case of typed XML. Static type errors are raised if an expression could fail at run time due to a type safety violation. Through the XQuery support, you can retrieve entire XML values or you can retrieve parts of XML instances. This is possible by using four XML data type methods that take an XQuery expression as argument: query(), value(), exist() and nodes(). A fifth method, modify(), allows modification of XML data and accepts an XML data modification statement as input. Here is a brief introduction to each of these methods:

• q query()-Extracts parts of an XML instance. The XQuery expression evaluates to a list of XML nodes. The subtree rooted at each of these nodes is returned in document order. The result type is untyped XML.

• q value()-Extracts a scalar value from an XML instance and returns the value of the node the XQuery expression evaluates to. This value is converted to a T-SQL type specified as the second argument of the value() method.

• q exist()-Performs existential checks on an XML instance. It returns 1 if the XQuery expression evaluates to non-null node list; otherwise it returns 0.

• q nodes()-Yields instances of a special XML data type, each of which has its context set to a different node that the XQuery expression evaluates to.

• q modify()-Enables you to modify parts of an XML instance, such as adding or deleting subtrees, or replacing scalar values such as the price of a book from 9.99 to 39.99.

Take a brief look at each of these methods. Before that, create a table named Department with two columns: an id column and xml_data column that uses the xml data type.

      CREATE TABLE Department(id int primary key, xml_data xml) 

Now that the table is created, insert a couple of rows to the table as follows:

      INSERT INTO Department values(1, '<department >      <name>Engineering</name><groupname>Research and      Development</groupname></department>')      GO      INSERT INTO Department values(2, '<department >      <name>Sales</name><groupname>Sales and Marketing</groupname></department>') 

Working with the query Method

With the introduction of the XML data type in SQL Server 2005, the FOR XML clause now provides the ability to generate an instance of XML directly using the new TYPE directive. For example:

            SELECT * FROM HumanResources.Employee as Employee FOR XML AUTO, TYPE 

This returns the Employee elements as an XML data type instance, instead of the nvarchar(max) instance that would have been the case without the TYPE directive. This result is guaranteed to conform to the well-formedness constraints provided by the XML data type. Because the result is an XML data type instance, you can also use XQuery expressions to query and reshape the result. For XQuery expressions, you use the query() method supported by the XML data type. For example, the following XQuery expression retrieves all the department names from the Department table.

      SELECT xml_data.query('/department/name') from Department 

The previous query results in the following output:

      <name>Engineering</name>      <name>Sales</name> 

You can also use the query method to execute a FLWOR XQuery. For example, you can build an XML document that contains all the department names from the Department table using this query:

      SELECT xml_data.query('        <department>        {          for $d in //department          order by $d/name[1]          return $d/name[1]          }        </department>')      FROM Department 

Here is the output produced by the previous query.

      <department><name>Engineering</name></department>      <department><name>Sales</name></department> 

Note that you must declare the namespace of the document if the source column is a typed xml column. Since the xml_data column is not a typed column, there is no need to use the namespace.

Working with the value Method

To the value method, you pass an XQuery statement and the return type. As a result, the value() method returns a single value that is produced as a result of query execution.

As an example, the following query,

      SELECT xml_data.value('      /department[1]/name[1]', 'VARCHAR(100)')      from Department Where ID = 1 

produces the following result.

      Engineering 

As you can see from the query, you pass in the data type as a second parameter to the value() method. This tells the method to return the value as that type. The value() method is very useful when you want to fetch a value from an XML column and insert the value into another table column of a different type.

Working with the exist Method

The exist() method allows you to determine whether a node value you are searching for exists. It returns a 1 if the node value is found and a 0 if not.

      Select xml_data.query('/department//name')      from Department where      xml_data.exist('/department[@id = "1"]') = 1 

In the previous example, the exist() method is used in the where clause and acts as the filtering mechanism to retrieve only the name for the department with the id value of 1. The query also produces “Engineering” as the output.

Working with the nodes Method

This method accepts an XQuery statement as a parameter and returns a rowset that contains logical scalar data from the XML variable. It is very similar to the selectNodes() function in XML DOM. This method is very useful when you need to shred the data from an XML data type variable into one or many relational table columns.

Working with the modify Method

Through the modify method, you can insert, update, or delete values from an XML typed column. To modify the contents of an element, you first need to reference that element using the XPath expression. Once you get to the actual element, you then reference the textual contents using the text() method as follows:

      Update Department      SET xml_data.modify        ('replace value of          (/department/name/text())[1]         with "Engineering and Design"          ')      Where ID = 1 

In the previous code, you use replace value of to identify the element you want to modify and then you use the with to specify the new value.

Now that you have had a look at the update, look at an example for deleting an element. For example, here is how you delete the <groupname> (child element of <department>) element from the xml_data column.

      UPDATE Department      SET xml_data.modify          ('delete /department/groupname[1]')      Where ID = 1