如何让 SQL Server XQUERY 返回除“没有名为 [Element] 的元素”之外的内容
如果在其他地方回答了这个问题,我们深表歉意。我不断收到错误消息 XQuery [Mytable.XMLData.nodes()]:没有名为“Answer”的元素
SELECT
ref.value('/','nvarchar(1000)')
FROM Mytable CROSS APPLY xmldata.nodes('Answer') R(ref)
-
--XML of Row
<Answer xmlns="http://TempNameSpace.com/AnswerData.xsd" Type="Deliverable">
<Deliverable>
<Title>test</Title>
<Description>test</Description>
<DueDate>2010-02-16T08:59:59</DueDate>
</Deliverable>
</Answer>
我尝试了几种不同的变体来获取根节点(“answer”)或任何子节点 但是,如果我将语句更改为读取,
SELECT
ref.value('/','nvarchar(1000)')
FROM Mytable CROSS APPLY xmldata.nodes('/') R(ref)
我会得到结果 testtest2010-02-16T08:59:59
我最终喜欢表格格式的数据,例如
SELECT
ref.value('/Title','nvarchar(1000)') as Title
ref.value('/Description','nvarchar(1000)') as Description
etc..
FROM Mytable CROSS APPLY xmldata.nodes('/Deliverable') R(ref)
感谢您的帮助
Apologies if this is answered elsewhere. I keep getting the error message
XQuery [Mytable.XMLData.nodes()]: There is no element named 'Answer'
SELECT
ref.value('/','nvarchar(1000)')
FROM Mytable CROSS APPLY xmldata.nodes('Answer') R(ref)
-
--XML of Row
<Answer xmlns="http://TempNameSpace.com/AnswerData.xsd" Type="Deliverable">
<Deliverable>
<Title>test</Title>
<Description>test</Description>
<DueDate>2010-02-16T08:59:59</DueDate>
</Deliverable>
</Answer>
I've tried several different variations on getting the root node ('answer'), or any of the child nodes
if, however i change my statement to read
SELECT
ref.value('/','nvarchar(1000)')
FROM Mytable CROSS APPLY xmldata.nodes('/') R(ref)
i get the result
testtest2010-02-16T08:59:59
I'd ultimately like this data in tabular format, something like
SELECT
ref.value('/Title','nvarchar(1000)') as Title
ref.value('/Description','nvarchar(1000)') as Description
etc..
FROM Mytable CROSS APPLY xmldata.nodes('/Deliverable') R(ref)
Thanks for your help
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您没有注意正在使用的 XML 命名空间:
您需要在查询时考虑到这一点 - 执行如下操作:
您需要使用
引用
XML 命名空间前缀。
内的所有内容ns:You're not paying attention to the XML namespace in play:
You need to take that into account when querying - do something like this:
You need to reference everything inside
<Answer>
with thens:
XML namespace prefix.