Using XQuery in MS SQL for Novices Part 2

How to use the value() function

Just the other day I started the conversation about how to use XQuery functions in SQL to pull specific XML data out of a table. It's time now for Part 2.

In the first discussion, I explained how to use the query() function. It's a very nice function because it returns both the XML nodes and the data within them. However, if all you want is the specific data in the node, having the nodes display in each result can make it more difficult to read the results. For this purpose we can use the value() function.

The value() function is ever-eager to throw the error "'value()' requires a singleton (or empty sequence)". It's also eager to throw the error "The value function requires 2 argument(s)." Obviously, these errors occur when we don't provide all of the necessary information.

The two arguments required by the values() function are:

1. The reference to the node
2. A data type specification in which to host the returned information

The requirement of a singleton is admittedly, not something I fully understand. It is solved, however, by appending a number one in brackets - [1] - to the end of the first argument. Just put everything inside of the single quotes and the argument itself inside of parentheses.

This is how it looks:

SELECT XMLColumn.value('(//XML-Node-Name)[1]', 'DataType(#)') FROM Table

Don't forget to add a comma between the arguments!
 
We can further tidy up the results by aliasing the column (assigning it a custom name of our choice) and using the exists() function.

SELECT XMLColumn.value('(//XML-Node-Name)[1]', 'DataType(#)') 'Alias Column Name' FROM Table WHERE XMLColumn.exists('//XML-Node-Name') = 1