Last time, I covered the EXIST Method. This time around I wanted to show you how to pull out values from XML stored in SQL. Let’s start with what should have been a simple example. I wanted to pull the first and last name for a job applicant along with their resume in XML format.
1: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')
2: SELECT
3: [resume].value('/Resume/Name/Name.First', 'VARCHAR(MAX)')
4: , [resume].value('/Resume/Name/Name.Last', 'VARCHAR(MAX)')
5: , [resume]
6: FROM HumanResources.JobCandidate
7: GO
The problem is I got an error:
Msg 2389, Level 16, State 1, Line 3 XQuery [HumanResources.JobCandidate.Resume.value()]: ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xs:string *’
When I looked up that error I found that it usually occurs when you haven’t narrowed the result to a single node. In other words it’s a repeating node. So I did a little check to find out if this was the case.
1: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')
2: SELECT
3: JobCandidateID
4: , SUM([resume].value('count(/Resume/Name/Name.First)', 'INT')) as NumberOfFirstNames
5: , SUM([resume].value('count(/Resume/Name/Name.Last)', 'INT')) as NumberOfLastNames
6: FROM HumanResources.JobCandidate
7: GROUP BY
8: JobCandidateID
When I looked through the results, no record had multiple first names or multiple last names. I thought I was going mad. According to the second query, the first query should have worked. Then I thought I should review the schema, perhaps multiple first and last names were allowed.
I found
<xsd:element name=”Name.First“ type=”xsd:string“ />
and
<xsd:element name=”Name.Last“ type=”xsd:string“ />
According to the schema, it would be valid to list several name elements, each with a first and a last name. So, with that in mind I altered my original query to overcome this, and only return the first first and last name for the resume.
1: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')
2: SELECT
3: [resume].value('(/Resume/Name/Name.First)[1]', 'VARCHAR(MAX)')
4: , [resume].value('(/Resume/Name/Name.Last)[1]', 'VARCHAR(MAX)')
5: , [resume]
6: FROM HumanResources.JobCandidate
7: GO
And the results were exactly what I had expected them to be, a list of first name, last name, and resume.
Now that I have the results I’m looking for, I want to cover how you can use VALUE on your own. Basically you call it with two parameters, the first is the XPath you’re looking for, the second is the data type you want to represent the results. If you get the error I got above, you may want to check to see how many instances of the XPath you’ve asked for occurs.
Also, please note you cannot use xml data type, a common language runtime (CLR) user-defined type, image, text, ntext, or sql_variant data type as the output of the VALUE method.
VALUE will be useful when you want to summarize data in an XML data store, or compute a useful metric based on XML data. Either way, learning when to use VALUE and when to use EXIST can be important. If you can conceive of a way to use EXIST rather than VALUE, the results will take less horsepower and time to complete.
But give it a try if you don’t believe me.
If you have any questions on VALUE, please send them in. I’m here to help you learn to use SQL more effectively!