SQL Server XQuery 返回错误 [英] SQL Server XQuery returns an error

问题描述

我正在对 SQL Server 2012 中的 XML 数据类型列执行查询.数据示例如下:

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;

错误:

消息 9314，级别 16，状态 1，第 2 行
XQuery [HumanResources.JobCandidate.Resume.value()]: 不能隐式原子化或应用 'fn:data()' 到复杂的内容元素，在推断类型 '(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()]: 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?

推荐答案

对于 SQL Server 表中的每一行，您的 XPath 表达式可能会导致返回多行.您需要使用 CROSS APPLY 并调用 .nodes() 来获取您想要的信息:

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)

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

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

如果您可以确定在您的 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:

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

这篇关于SQL Server XQuery 返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！