Talk to the Experts

In the last post, I discussed the query() method in my XQuery for the Non-Expert series.  With this next post, we’ll be working on taking data out of the XML fragments and placing them into columns with the value() method.

That’s what we really want – to take the XML document and get some row and columns out of it.  Where the nodes() method brought out the rows, the values() method will bring on the columns.

Value Syntax

The general syntax for the query() method is:

value (XQuery, SQLType)

Another method with fairly simple syntax.  There are two variables for this method:

• XQuery – Statement that directs the value() method to the element or attribute in the XML document should be returned as a column.  For a more detailed explanation of the XQuery statement,read my post on the nodes() method.
• SQLType – A string representing the data type that should be returned from the value() method.  The type can be any SQL Server data type except for xml, common language runtime (CLR) user-defined type, image, text, ntext, or sql_variant data type.

The value() function is called as a method off of the column that contains the XML data.  This method is called from the SELECT statement.

Value Setup

To illustrate how to use the value() method let’s setup a table with an XML document.  Run the following query to get everything setup for the examples below:

IF OBJECT_ID('tempdb..#ValueExample') IS NOT NULL
DROP TABLE #ValueExample
GO
CREATE TABLE #ValueExample
(
XMLDocument xml
)
INSERT INTO #ValueExample
VALUES ('<Building type="skyscraper">Sears Tower
<Floor level="1" />
<Floor level="2" />
<Floor level="3">
<Room number="3.1" />
<Room number="3.2" />
</Floor>
<Floor level="4">
<Room number="4.1" />
<Room number="4.2" />
</Floor>
</Building>')
SELECT XMLDocument FROM #ValueExample

Time to bring on the columns with the value() method.

Value Attribute Example

For the examples, we’ll go through situations of how data in the XML document can be taken out and returned as a column value.  For us DBAs, this is the money shot in XQuery that will gives us something legible and informative to work with.  XML isn’t like The Matrix – staring at it long enough doesn’t make it any easier.

In this first example, the query will return the number attribute from each of the Room elements.  The nodes() method will be used to return one row for every Room element.  To access an attribute within an element with the value() method, prefix the attribute name with the @ symbol.

The query for this will look like the following:

SELECT c.query('.') AS XMLFragment
,c.value('(@number)','decimal(3,1)') AS RoomNumber
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor/Room') as t(c)

The results of this will be the following:

In the example, the nodes() method was browsing down to the an attribute in the root of the new XML fragment.

There are some circumstances where it might make sense to go to the Floor element and still return Room element number attribute.  The query to do this might  look like this (if you see a problem with it already, consider my use of might as foreshadowing)”:

SELECT c.query('.') AS XMLFragment
,c.value('(Room/@number)','decimal(3,1)') AS RoomNumber
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

Running this query will result in the following error message:

Msg 2389, Level 16, State 1, Line 2
XQuery [#ValueExample.XMLDocument.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

This error occurs because XQuery can’t determine if there should be a 1:1 or 1:many relationship between the Floor and Room elements.  It doesn’t know which Room element to use to return the number attribute.

Fixing the error requires the use of the index value to guarantee that a singleton attribute is returned.  To do this, add brackets to the XQuery in the values() parameter.  The query would be changed to the following:

SELECT c.query('.') AS XMLFragment
,c.value('(Room/@number)[1]','decimal(3,1)') AS RoomNumber1
,c.value('(Room/@number)[2]','decimal(3,1)') AS RoomNumber2
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

The execution will succeed with the following result set:

For this XML document, having a separate value() method call for each room on a floor may not be idea.  In some cases, this type of a call may be useful and necessary.

Value Element Example

The next example to take a look at is how to return a value from the within the element tags.  In the XML document for these examples, the Building element contains information on the name of the building.

There will be times when this information need to be returned in a column.  This can be accessed with a query like the following:

SELECT c.query('.') AS XMLFragment
,c.value('(.)','nvarchar(max)') AS RoomNumber
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building') as t(c)

Of course, as expected the results would include the Sears Tower as a column value:

The period is used to designate that the element value to return is at the level that the nodes() method has browsed to.  Deeper XQuery statements and the singleton designations, as with attribute examples above, apply here as well. The chief difference between the two uses is the inclusion of the @ symbol.

Less Than Success

As I’ve learned and played with XQuery, there are a few other methods that I’ve been tempted to use to get this information.  These examples won’t provide the desired results, but let’s discuss them to cross a few mistakes off the list.

The first, there is the temptation to include the root level element name in the value() method.  To demonstrate this, the query will use nodes() to get to the Room element and then use the value() method to return the number attribute.  The query will include an unnecessary XQuery path to the Room element.

This query would be:

SELECT c.query('.') AS XMLFragment
,c.value('(Room/@number)[1]','decimal(3,1)') AS RoomNumber
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor/Room') as t(c)

As you can see, in the value() method there is the extra Room XQuery statement.  The results also don’t provide the desired results:

Besides the incorrect results, an indication that this may be about to occur was that the error singleton error from above occurred in the first iteration of this query.  I had to add the index value to force a singleton to be returned.

Another mistake that can be made is the includes of a couple forward slashes(“//”) in the XQuery statement for the value() method.  As discussed in the nodes() method post, double forward slashes causes the XQuery statement to search the entire XML document instead of the current XML fragment.

A query doing this would look like the following:

SELECT c.query('.') AS XMLFragment
,c.value('(//Room/@number)[1]','decimal(3,1)') AS RoomNumber
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor/Room') as t(c)

A couple forward slashes, what harm could they really cause?  Looking at these results, the answer is a lot.

In these results, the number attribute for the first Room element has been repeated for all rows in the result set.  This could be a serious issue, especially if the results from the XML document was controlling your annual bonus.  As mentioned above, if you need to index the values statement to get a singleton value make certain you are expecting the possibility for 1:many values for the row being returned.

Why discuss these incorrect methods for getting to the results?  As the XML documents being queried with XQuery get more complicated, the incorrect methods may appear to work, but will be returning incorrect results.

Please let me know if you have any other mistakes to include.  I’ll add to this post or create a follow-up post to help others learn from our mistakes.

Value Wrap-Up

That’s all she wrote (or he wrote) for the value() method.  Hopefully these examples will help get you started with shredding some XML and getting some old fashioned column based results.

If anything in this post needs further clarity, please comment below.  I expect that as time goes on I will update and expand this post or create a follow-up as I learn more about how to leverage the value() function.

Related posts:

1. XQuery for the Non-Expert – Query
2. XQuery for the Non-Expert – Nodes
3. XQuery for the Non-Expert – Terminology