SQL Server XQuery returns an error

Question

I am querying an XML data type column in SQL Server 2012. Data example:

<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"> <ns:Name> <ns:Name.Prefix></ns:Name.Prefix> <ns:Name.First>Shai</ns:Name.First> <ns:Name.Middle></ns:Name.Middle> <ns:Name.Last>Bassli</ns:Name.Last> <ns:Name.Suffix></ns:Name.Suffix> </ns:Name> ... </ns:Resume> 

I am trying to write a query to return the first names.

This query returns a list of first names as expected:

 WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns) SELECT [Resume].query('(//ns:Name.First)').value('.[1]', 'nvarchar(100)') FROM HumanResources.JobCandidate; 

However, this request returns an error:

 WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns) SELECT [Resume].value('(//ns:Name.First)[1]', 'nvarchar(100)') FROM HumanResources.JobCandidate; 

Error:

Msg 9314, Level 16, State 1, Line 2
XQuery [HumanResources.JobCandidate.Resume.value ()]: It is not possible to implicitly spray or apply "fn: data ()" to complex content elements, find the type "xs: anyType" in the inferred type "(element (ns { http: // schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume }: Name.First, xs: string) | element (ns { http://schemas.microsoft.com/sqlserver/2004/07/adventure- works / Resume }: Name.First, xs: anyType))? '.

There is some basic understanding that I'm missing here, but I'm not sure what it is. Can someone enlighten me? Why does the second query return an error?

Answer 1

Your XPath expression may return multiple rows for each row in a SQL Server table. To get this information, you need to use CROSS APPLY and the .nodes() call:

 WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns) SELECT JobCandidateID, ResNames.value('(ns:Name.First)[1]', 'nvarchar(100)') FROM HumanResources.JobCandidate CROSS APPLY [Resume].nodes('/ns:Resume/ns:Name') AS XTbl(ResNames) 

This should return all JobCandidateID values ​​and all names defined in the Resume XML column for each row of the table.

If you can be sure that your XML column will have only one <name> , you can also shorten it to:

 WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns) SELECT JobCandidateID, [Resume].value('(/ns:Resume/ns:Name/ns:Name.First)[1]', 'nvarchar(100)') FROM HumanResources.JobCandidate