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:
- The reference to the node
- A data type specification in which to host the returned information
This is how it looks:
SELECT XMLColumn.value('(//XML-Node-Name)', 'DataType(#)') FROM TableDon'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)', 'DataType(#)') 'Alias Column Name' FROM Table WHERE XMLColumn.exists('//XML-Node-Name') = 1