SQL Server XQuery returns an error

Question

I am performing a query against an XML data type column in SQL Server 2012. An example of the data is:

<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 query 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()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 'xs:anyType' within 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's 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?

marc_s · Accepted Answer · 2013-06-29 13:58:00Z

1

Your XPath expression could lead to multiple rows being returned, for each row in the SQL Server table. You'll need to use a CROSS APPLY and a call to .nodes() to get that information you're after:

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)

That should return all JobCandidateID values and all first names defined in the Resume XML column for each row in the table.

If you can be sure that there's only ever going to be a single <name> tag in your XML column, then you could also shorten this 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

answered Jun 29, 2013 at 13:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RBarryYoung Over a year ago

I am sure that you are correct marc_s, but things like this in XML always throw me for a loop. Can you explain why the '..[1]' already present doesn't address the problem of possibly returning more than one item per row? thnx..

marc_s Over a year ago

@RBarryYoung: yes, I thought that [1] would do the trick, too! But it appears (from the error message thrown) that by searching for //ns:Name.First somehow the XQuery processing in SQL Server assumes there's a Name.First,xs:string and a second Name.First,xs:any in the XML schema collection (again: I'm not sure why that happens - I've just seen it happen on occasion). In that case, the XQuery processor cannot figure out what the Name.First element is (what type) and thus barfs up

Seiker Over a year ago

So using '(/ns:Resume/ns:Name/ns:Name.First)[1]' instead of '(//ns:Name.First)[1]' makes the difference? Why?

marc_s Over a year ago

@Seiker: because you're more explicit about what you want - you're not asking for any <name> anywhere in the whole XML document - you're asking for a very specific, very clearly defined value

RBarryYoung Over a year ago

sigh every time I think I have SQL server XQuery figured out, I don't. :-(

|

Collectives™ on Stack Overflow

SQL Server XQuery returns an error

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related