Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Working with XQuery Methods
With SQL Server 2000, you could convert relational data to XML using the T-SQL For XML clause at the end of a Select statement. With SQL Server 2005, you no longer need to do this because you can store XML directly to the database by using a new data type called ’xml’ when creating a table column. A table column of this type can hold either fragments or entire well-formed XML document. Along with the new xml data type comes a new set of methods for exposing the data this type contains: Query, Value, Exist, Modify, and Nodes. I’ll illustrate by going through a basic example with each method. Let’s say we have an XML document stored to a column called AuthorXData of a table called Authors.
Using the Query method
To retrieve the name of an author with a certain id, the following XQuery statement can be used:
SELECT AuthorXData.query(’data(/root/authors/[@au_id="238-95-7766"]/title)’) FROM Authors
The dot notation is used to associate the query method to our column name. We pass the query method’s data function an xpath statement telling the query to retrieve the title for an author having an author id of 238-95-7766. Each slash in the xpath statement represents an index into the xml document. First we start at root and scan each authors looking at the au_id attribute to see whether it matches the one in our query. After the au_id is found, we index into the title element (child of the author element) pulling the title value. The above example works for untyped XML data. For typed XML data using a registered schema, you must declare the namespace for that schema:
SELECT AuthorXData.query(’declare default namespace http://schemas.myserver.com/sqlserver/2005/authors; data(/root/authors/[@au_id="238-95-7766"]/title)’) FROM Authors
A schema defines which elements and attributes are allowed in your XML and the order in which those elements appear, along with other characteristics. A schema collection in SQL Server 2005 is used as a placeholder for grouping schemas. A schema collection can be created using the CREATE XML SCHEMA COLLECTION T-Sql statement. After you create a schema collection, you can add schemas to that collection.
Using the Value Method
Suppose that you want to fetch a value from an XML column and insert the value into another table column of a different type. For example, what if I want to store the author title as a string in another table containing the column Title of type varchar(150). The Value method will accomplish this by setting up a return type we define for the value.
SELECT AuthorXData.value(’data((/root/authors/[@au_id="238-95-7766"]/title[1])’, ’varchar(150)’) FROM Authors
We pass in the data type as a second parameter to the Value method. This tells the method to return the value as that type. Also included is an indexer ([1]) in the statement for telling the method to pull only one element value. In this example, it isn’t necessary as we are pulling the element value based on a unique author id. There might be instances where you have elements that have the same attribute value, so specifying which one to extract is necessary.
Using the Exist Method
This method is used to see whether a node value you are searching for exists or not. It returns a 1 if the node value is found and a 0 if not. An example appears below:
SELECT AuthorXData.exist(’data(/root/authors/[@au_id="238-95-7766"]/title[1])’) FROM Authors
Most often, you’ll use this method to filter out empty values of the XML data you are evaluating. You can also use the Exist method in a Where clause to return XML data based on a condition:
SELECT AuthorXData.query(’data(/root/authors/title)’) From Authors Where AuthorXData.exist(’data(/root/authors/[@au_lname="Smith"])’) = 1
In this example, all titles are scanned in the authors element having the last name "Smith" as the attribute value for the au_lname attribute. Any elements that don’t meet the condition in the Where clause (don’t have the author last name as Smith) are skipped. Likewise, you could set the Exist statement to 0 and filter out all elements having the last name "Smith". You could have put this all in one Xpath statement as in earlier examples, but using the Exist method in this manner can improve performance if we are traversing a large amount of XML data by looking only at elements that meet our criteria.
Using the Modify Method
The Modify method allows you to update your XML by removing, replacing, or adding nodes and values. As with vintage SQL, you can use the UPDATE statement with the Modify method to accomplish this. In the following example, the authors that have the last name "Ellis" are removed from the XML column.
Update Authors Set AuthorXData.modify(’delete /root/authors/[@au_lname="Ellis"]/title’)
To delete all the title elements in the XML column, do the following:
Update Authors Set AuthorXData.modify(’delete /root/authors//title’)
To insert a new XML node into the XML column, specify the element you want this node to be a member of. You can either seek out one or a collection of elements. Also, if the target node has more than one child element, you must also specify the position that the new node will occupy. In the following example, an address is added as a child element to the authors element having the last name Olson for the last name attribute. Because the authors element already contains a child element (titles), the address element is inserted after the titles element in position 2.
Update Authors Set AuthorXData.modify(’insert <address>1405 Cherry Lane</address> as second into (/root/authors/[@au_lname="Olsen"])[1]’)
Notice that the Insert statement is used to add new XML. To replace some XML, use the Replace statement. The following example replaces the title value for the title element of the author element having the last name Olsen.
Update Authors Set AuthorXData.modify(’’declare default namespace http://schemas.myserver.com/sqlserver/2005/authors; replace value of (root/authors/[@au_lname="Olsen"])[1]’ with "This is now a different title")
A namespace had to be declared because the Replace method works only on typed data to make sure all types are the same.
Using the Nodes method
The Nodes method is a convenient way for you to extract nodes or node values from an XML column and place them in a new table. You might want to do this if you are constructing new XML columns that will contain the same subset of nodes or use the values with relational data. For example, let’s say I want to pull all the titles from the XML column AuthorXData and place them in a new table called Titles. A new column called Title is automatically created in this new table. To accomplish this, the following Xquery statement is used with the Nodes and Value methods:
SELECT AuthorXData.value(’data((/root/authors/titles)’, ’varchar(150)’) From Authors AS Titles(Title)
You can use any of the previous methods with the node method to extract the data. Use the WHERE clause to place conditions such as filtering out certain values.