The XML Methods in SQL Server

The XML Data type has definite uses, but the way of interrogating, retrieving, and manipulating the values of properties and attributes within XML have been so foreign to the SQL language as to be somewhat of a barrier to their use. Fortunately, Robert Sheldon has once more managed to make the subject accessible to those of us who just need to get the job done.

In my last article, “Working with the XML Data Type in SQL Server,” I included examples that returned data from columns and variables of the XML data type. Based on those examples, it might seem that retrieving XML data is similar to retrieving objects configured with other data types. However, that’s true only when returning the entire XML value. If instead you want to work with individual components within an XML instance, such as  when retrieving the value of a single attribute, you must use one of the five methods that available to the XML data type-query(), value(), exist(), nodes(), or modify().

To use an XML method, you call it in association with the database object configured with the XML data type, as the following syntax illustrates:

The DbObject placeholder refers to a column, variable, or parameter configured with the XML data type. After the object name, you add a period, following by the name of the XML method. Next you provide, in parentheses, the arguments required for the specific method.

Most of the XML methods require only one argument: an XQuery expression that identifies the XML components to be retrieved or modified, and any actions to be taken on those components. XQuery, a powerful scripting language used specifically to access XML data, contains the elements necessary to create complex expressions that can include functions, operators, variables, and values. MSDN provides a complete XQuery language reference that you can refer to while you work with the XML methods.

In addition to the XQuery expression, an XML method might also require a Transact-SQL data type as a second argument or a table and column alias tagged on after the arguments. As we work through the article, you’ll see when and why these additional components are necessary.

To demonstrate how the various XML methods work, I’ve written a number of examples that use the methods to retrieve and modify data. The examples are based on the Stores table, which I created and populated with the following Transact-SQL script:

Notice that the table includes the Survey_untyped column, which is an XML untyped column, and the Survey_typed column, which is an XML typed column. The schema collection I associated with the typed column is already included in the AdventureWorks2008R2 database, which is where I’ve created the table. The INSERT statement in the Transact-SQL above also shows the XML document that I added to the untyped column. For the typed column, I retrieved the following XML document from the Demographics column (a typed XML column) in the Sales.Store table in the AdventureWorks2008R2 database:

Notice that the XML includes the schema associated with that data. The schema comes from the same schema collection I used when I created the typed column in the Stores table.

The data I added to the untyped column is nearly identical to the typed XML. The untyped XML doesn’t include the namespace information, of course. However, it does include additional product information, which we’ll use to demonstrate the XML methods. So now that we have setup out of the way, let’s look at how those methods work.

The query() method

The query() method retrieves a subset of untyped XML from the target XML instance. It’s probably the simplest and most straightforward of the XML methods. You need only specify the database object, the method name, and an XQuery expression, as shown in the following syntax:

You’ll notice that the XQuery expression is entirely enclosed in single quotes and those are enclosed in parentheses. The trick with any XML method is to get the XQuery expression right. But at its simplest, the expression must define the path of the element you want to retrieve.

In the following example, I use the query() method to return data from the <StoreSurvey> element in both the typed and untyped columns:

For the untyped column, I specified the column name (Survey_untyped) followed by a period and then the method name (query). Within the parentheses and single quotes, I defined the XQuery expression (/StoreSurvey). That’s all there is to it. Because the <StoreSurvey> element is the root node, the entire XML document is returned.

The XQuery expression for the Survey_typed column is a bit more complex. Because it is a typed column, the expression should be preceded by a namespace declaration. The namespace must be the same as the one referenced within the XML document stored in the typed column.

To declare a namespace, you specify the declare namespace keywords, followed by the alias name (in this case, ns). After the alias, you include an equal sign (=) and then the schema path and name. You then end the declaration with a semi-colon (;) to separate it from the main body of the XQuery expression. After you declare the namespace, you can use the alias within the expression to reference that namespace.

The expression itself is nearly identical to the one used for the untyped column, except that you must precede the element name with the namespace alias and a colon (ns:). As with the untyped column, the expression will return the entire XML document because it specifies only the root node.

Although the preceding example is helpful in demonstrating the basics of using the query() method, it’s not much use beyond that because you can just as easily retrieve all the column contents simply by specifying the column name. However, in the following example, I get more specific by limiting the results to the <AnnualSales> child element:

The only difference between this example and the preceding one is that I added /AnnualSales to the XQuery expression for the untyped column and /ns:AnnualSales to the expression for the typed column. The XQuery for the untyped column returns the following results.

And the XQuery for the typed column returns these results:

The primary difference between the two is that the data returned by the typed column includes the namespace information. The element values themselves (800000) are the same in both columns. If the <AnnualSales> element had included its own child elements, those too would have been displayed. For instance, the following example retrieves the <Products> elements, which are child elements of <StoreSurvey>, just like <AnnualSales>:

Because the XML document includes two <Products> elements and those elements each include several <Product> child elements, the SELECT statement returns all product-related elements, as shown in the following results:

If you want to return a specific element and its child elements, you can do so by referencing one of its attributes. For example, suppose you want to include only products from the Bikes category. To do so, you can modify your XQuery expression as follows:

Now the XQuery expression includes bracketed information that specifies the Type attribute and its value. The attribute name is preceded by the at (@) symbol, and the attribute value is preceded by an equal sign (=) and enclosed in double quotes. As a result, the SELECT statement now returns the following XML fragment:

As you would expect, only elements whose products are in the Bikes category are returned. You could have just as easily specified “Clothes” rather than “Bikes,” and you would have received the product elements related to clothing.

The value() method

The value() method returns a scalar value from the targeted XML document. The returned value is converted to the data type you specify when you call the method. The value() method makes it easier to work with individual values in order to compare or combine them, either with other XML values or values of different types.

As the following syntax illustrates, when you call the value() method, you must specify the XQuery expression and the Transact-SQL data type for the returned data:

For example, suppose you want to pull the amount of sales from the <AnnualSales> element in either the typed or untyped column in the Stores table. You can specify the path in the XQuery expression plus a numerical data type as the second argument, as shown in the following SELECT statement:

For the untyped column, the XQuery expression includes the element path as you saw in earlier examples. However, after the path, you must add an integer in brackets that indicates which element of that name you should retrieve. More often than not, you will simply add [1], as I’ve done here. This assures that, if there are multiple elements with the same name, only the first one will be returned. It also assures that only one element is being referenced at a time. In fact, even when there is only one element, as is the case with <AnnualSales>, you must still include the [1] because the value() method expects a singleton value.

The second argument passed into the value() method is the name of the data type, in this case, int. That means an int value will be returned by the method. Note that the method’s two arguments must each be enclosed in single quotes and separated with a comma.

As for the typed column, the namespace declaration and path are also similar to what you’ve seen in earlier examples. The only difference is that this expression must also include the [1], just as we did it for the untyped column. As a result, for each column, the SELECT statement returns a single int value of 800000, without any additional element information.

You can also retrieve an attribute value, rather than an element value, by specifying the name of the attribute in your element path. For example, the following SELECT statement retrieves the value of the Type attribute for the second instance of the <Products> element:

The expression includes [2], rather than [1], in order to retrieve data from the second instance of <Products>. As a result, the SELECT statement now returns the varchar value Clothes.

As stated earlier, the value() method is also handy if you want to combine or compare data. In the following example, I create a calculated column that’s based on two values returned from the untyped column:

The first instance of value() retrieves the <AnnualSales> value. The second value() instance retrieves the <AnnualRevenue> value. The second value is then subtracted from the first value to return a scalar value of 720000.

You can also use XQuery functions in your expressions. For instance, in the following example, I use the concat function to add a string to the <Specialty> value:

When you use the concat function, you specify each element that you want to concatenate as an argument to the function and separate those arguments with a comma. The statement returns the value Bike specialty: Mountain.

As you can see, the string has been concatenated with the <Specialty> value. I could have just as easily concatenated multiple element values or added more string values.

The exist() method

The exist() method lets you test for the existence of an element or one of its values. The method takes only one argument, the XQuery expression, as shown in the following syntax:

The key to using the exist() method is in understanding the values it returns. Unlike the query() and value() methods, the exist() method doesn’t return XML content. Rather, the method returns one of the following three values:

  • A BIT value of 1 if the XQuery expression returns a nonempty result
  • A BIT value of 0 if the XQuery expression returns an empty result.
  • A NULL value if the XML data type instance is null.

A good way to test how the exist() method works is to use a variable to capture the method’s results, as I’ve done in the following example:

First, I declared the @xml variable as type XML. Then I declared the @exist variable as type BIT. I set the value of @xml to equal the XML document in the Survey_untyped column. I then used the exist() method on the @xml variable to test for the existence of /StoreSurvey[BusinessType="BM"] within the XML document. In other words, the exist() methods checks whether the <BusinessType> child element exists and whether it contains a value of “BM”.

I then assigned the results returned by the exist() method to the @exist variable and used a SELECT statement to return the contents of the variable. Because the XML document contains this child element and that element has a value of “BM”, the method returns a value of 1. However, if I change “BM” to “BMX”, as I’ve done in the following example, the method returns 0 because the element and value don’t exist exactly as specified:

Now that you have a sense of how the exist() method works, let’s look at how you might use it in your queries. In the following example, I include a WHERE clause that uses the exist() method to again test for the existence of /StoreSurvey[BusinessType="BM"]:

The WHERE clause compares the results of the exist() method to the number 1. If the results equal 1, that is, if the XML contains the specified element and values, the WHERE clause evaluates to True and the SELECT statement returns the product information, as shown in the following results:

If the exist() method had specified a different <BusinessType> value, as it does in the following example, the SELECT statement would instead return an empty result set:

Again, the key to the exist() method is to remember that it returns only one of three values: 1, 0, or NULL.

The nodes() method

The nodes() method can be a bit more slippery to understand than the other XML methods. To begin with, rather than returning XML or scalar values, the nodes() method returns what is essentially a table that includes one column. That means you should use the method only in those parts of a statement that can handle rowset views, such as the FROM clause. It also means that, when you call the nodes() method, you must assign a table alias and column alias to the rowset view returned by the method, as shown in the following syntax:

The table and column aliases let you reference the rowset view in other parts of the statement. The method is useful when you want to shred an XML document, that is, decompose the document into a relational format. To better understand how this works, let’s look at an example that uses the nodes() method on an XML variable:

First, I declared the @bikes variable with the XML data type. Then I set its value to equal the XML fragment. In the FROM clause, I invoked the nodes() method on the variable to shred the <Product> elements. My goal was to retrieve the value from each instance of that element. I then provided a table alias (Bike) and a column alias (Category) to identify my rowset view. Now I essentially have a table with a single column that contains a row for each <Product> element.

When you use the nodes() method to create a rowset view of the XML data, you have to use one of the other XML methods to retrieve the contents of that view. In this case, I used the query() method in the SELECT list to return the value of each row. Notice that to retrieve the value I used a period to reference the current context node and the text() node function to retrieve only the element values, as shown in the following results:

If you want to use the nodes() method to retrieve data from a table, you can use the CROSS APPLY operator in the FROM clause to associate the table with the rowset view returned by the method, as shown in the following example:

In this case, I specified the XQuery path as StoreSurvey/Products[@Type="Bikes"]/Product' in order to return only the bike-related products from the Survey_untyped column. However the SELECT list itself is the same as the preceding example, so this statement returns the same results as that example.

The modify() method

The methods we’ve looked at up to this point have been concerned only with reading data in an XML document, but the modify() method lets you update that data. As the following syntax shows, the only argument you pass into the modify() method is the XQuery expression:

In this case, however, the XQuery expression is actually a special type of expression that uses the XML Data Modification Language (XML DML), which is a SQL Server extension to XQuery. The modify() method lets you define XML DML expressions that can add, update, or delete elements within an XML document.

NOTE: This section covers only the basics of the modify() method. You can find more information about the method and its uses in the XML Data Modification Language Workbench, which provides additional details and examples about modifying XML data.

Adding components to XML data

To add components to your XML data, you must specify the insert keyword and target location in your expression. For instance, the following UPDATE statement adds the <Comments> element to the typed and untyped XML columns in the Stores table:

For the Survey_untyped column, I first specified the modify() method name, followed by the XML DML expression, which I enclosed in parentheses and single quotes, just like the other XML methods. Within the expression, I included the insert keyword and the element I wanted to add: (<Comments>Largest bike store in region</Comments>). Notice that I enclosed the element in parenthesis and included the element’s value.

Next, I added the after keyword and specified the location of where to add the new element. I also included the [1] because the method requires a singleton value. Now the new element will be added after the first instance of the /StoreSurvey/NumberEmployees element.

As you would expect, modifying the Survey_typed column followed the same process, except that I also included the namespace declaration and aliases. An important issue to consider, however, when working with typed XML columns is that any changes you make must conform to the schema that governs the XML content. For example, if I had tried to add the <Comments> element to any other location within the document, I would have received a violation error. However, if you’ve set up your statement properly and adhered to the schema, the new element should be added to the XML document with no problem.

Updating components in XML data

To use the modify() method to update XML data, you must include the replace value of keywords, rather than the insert keyword. You must then specify the component you want to update as well as the updated information. For example, in the following UPDATE statement, I change the value of the <Comments> element:

After I specified the replace value of keywords, I added the element path along with the text() node function, which let me change only the element’s value (without affecting the element itself). And as with the previous example, I also included a [1] because a singleton value is expected. Next, I specified the with keyword and the new element value, enclosed in double quotes. As a result, when I ran the statement, it replaced the old element value with the new one.

Deleting components from XML data

To use the modify() method to remove data from an XML document, you must specify the delete keyword, followed by the component you want to delete, as shown in the following example:

In this case, it was simply a matter of specifying the path to the <Comments> element, in parentheses, after the delete keyword. Of course, all this was followed by [1] to keep the database engine happy.

The XML Methods

The methods available to the XML data type provide you with a set of powerful tools for working with XML data. And as you can see from the examples, most of that power rests in your ability to create XQuery expressions that target the information you want to access. Yet the expressions shown here are relatively basic when compared to how extensive the XQuery language is. In fact, to make the most of what the XML methods offer, you must invest the time necessary to understand the various elements that make up that language. Until then, what I’ve shown you here should provide you with the first steps necessary to start accessing and updating your XML data. Just know that there’s a much bigger universe out there waiting to be discovered.

Tags: , , ,

  • 162858 views

  • Rate
    [Total: 7    Average: 4.7/5]
  • marc_scheuner

    Excellent read !
    Great stuff, Robert – as per usual! I’ll have to bookmark this and send all the folks here that need to learn about XML support in SQL Server. Thanks!

  • Paul Richmond

    One of the cleanest treatments of the subject I’ve seen.
    Great job Robert! Very clear and presented in clear, incremental steps. I forwarded the link to my whole team.

  • zgunay

    Tnx
    Thanks Sheldon

  • Murali

    Simple but clear explanation
    Thanks for the wonderful post Robert. I have been searching for such an article for sometime to understand the basics of XML in SQL Server and your article has really helped me in understanding the basics of the same.

  • Anonymous

    awesome!
    thank you very much

  • uwillmore

    a question
    Robert, I am new to XML and want to know what happens if the data type of an element in the schema is different from the data type used when selecting the element. For example: The schema defines an element like this:
    <xsd:element name="ObserverID" od:sqlSType="float" type="xsd:double" />
    The stored procedure that reads the XML expects ObserverID to be of type int. Sample values of ObserverID are numbers such as 38244, 2543 or 11, and @ObserverID is declared to be of type int. @ObserverID is then used to get the element ObserverID from the XML, using this code:
    SET @ObserverID = (SELECT a.b.value(‘ObserverID[1]’,’nvarchar(255)’) FROM @XML.nodes(‘/myFields’) a(b))

    My question is this:
    What value will @ObserverID have when the SELECT finishes?

    Thanks for any help you can give.
    Before I close, I want to thank you for an excellent article on XML Methods. I’ve read several of your articles on XML and found them all very helpful, but XML Methods in SQLServer is the best of the bunch! It really helped me to understand the code I am working on. I just couldn’t figure out what happens when the data types don’t match up. Hopefully you can answer that question for me.
    Looking forward to you reply.

    URW

  • uwillmore

    a question
    Robert, I am new to XML and want to know what happens if the data type of an element in the schema is different from the data type used when selecting the element. For example: The schema defines an element like this:
    <xsd:element name="ObserverID" od:sqlSType="float" type="xsd:double" />
    The stored procedure that reads the XML expects ObserverID to be of type int. Sample values of ObserverID are numbers such as 38244, 2543 or 11, and @ObserverID is declared to be of type int. @ObserverID is then used to get the element ObserverID from the XML, using this code:
    SET @ObserverID = (SELECT a.b.value(‘ObserverID[1]’,’nvarchar(255)’) FROM @XML.nodes(‘/myFields’) a(b))

    My question is this:
    What value will @ObserverID have when the SELECT finishes?

    Thanks for any help you can give.
    Before I close, I want to thank you for an excellent article on XML Methods. I’ve read several of your articles on XML and found them all very helpful, but XML Methods in SQLServer is the best of the bunch! It really helped me to understand the code I am working on. I just couldn’t figure out what happens when the data types don’t match up. Hopefully you can answer that question for me.
    Looking forward to you reply.

    URW

  • deepujagan

    Help
    Hi,
    I am looking for some help,please anyone to help me to gett he output I wanted??
    The output I need is
    <data>
    <variable name = "user defined varibalename">
    <row>
    <column>…</column>
    <column>…</column>
    </row>
    <row>
    <column>…</column>
    <column>…</column>
    </row>
    </variable>
    </data>

    The output Im geeting fromt eh SQL Stored Procedure is
    <data>
    <row>
    <column>…</column>
    <column>…</column>
    </row>
    <row>
    <column>…</column>
    <column>…</column>
    </row>
    </data>

    Thank you and really appreicate your time and help.

    Thank you
    Jagan

  • Chris

    does syntax exist to express IN LIST in the exist function? e.g.

    where t.XmlCol.exist(‘row[BikeType IN (sql:variable(“@P1”),sql:variable(“@P1”))]’) = 1

  • Gonzalo

    Hi guys,

    Please I am in an urgency. I need to solve a headache with XML codes. How can i insert an outer tag in a select no depending of any column?

    ————

    xxxxxxx
    xxxxxx

    ———–

    I get an structure similar to below after a select for xml, but in order to correspond to the structure of an applicacion working with the output, I need to insert a !!!. The select query for XML is already done, but I just need to insert this outer tag4 quite similar like in the sample. Is there any method or resource for inserting a tag without depending of another sub query or column?????

    I greatly appreciate any response

    Gonzalo