SQL Server XQuery返回错误

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

我正在对SQL Server 2012中的XML数据类型列执行查询。数据的一个示例是:

<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)) ?'.

Msg 9314,Level 16,State 1,Line 2 XQuery [HumanResources.JobCandidate.Resume.value()]:不能隐式地将'fn:data()'原子化或应用于复杂的内容元素,在推断中找到'xs:anyType'类型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?

我有一些基本的理解,我在这里失踪,但我不确定它是什么。有人可以开导我吗?为什么第二个查询返回错误?

1 个解决方案

#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:

对于SQL Server表中的每一行,您的XPath表达式可能会导致返回多行。你需要使用CROSS APPLY和对.nodes()的调用来获取你所追求的信息:

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.

这应返回表中每行的所有JobCandidateID值和Resume XML列中定义的所有名字。

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:

如果您可以确定XML列中只有一个标记,那么您还可以将其缩短为:

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

#1