在 SQL Server 中使用 value() 从 xml 列获取多条记录
此 SQL 仅返回第一个 Activity 元素。我该如何选择它们?如果我删除查询中的 [1],则会收到“value() 需要单例”的错误。
DECLARE @myDoc xml
SET @myDoc =
'<Root>
<Activities>
<Activity>This is activity one</Activity>
<Activity>This is activity two</Activity>
<Activity>This is activity three</Activity>
</Activities>
</Root>'
SELECT @myDoc.value('(/Root/Activities/Activity)[1]', 'varchar(100)' )
This SQL only returns the first Activity element. How do I select them all? If I remove the [1] in the query I get an error that "value() requires a singleton".
DECLARE @myDoc xml
SET @myDoc =
'<Root>
<Activities>
<Activity>This is activity one</Activity>
<Activity>This is activity two</Activity>
<Activity>This is activity three</Activity>
</Activities>
</Root>'
SELECT @myDoc.value('(/Root/Activities/Activity)[1]', 'varchar(100)' )
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
谢谢埃德,但我找到了一个更简单的版本:
虽然从你的“不必要的复杂”示例来看,它似乎简单得令人担忧......
Thanks Ed, but I found an easier version:
Though from your "unnecessarily complex" example it seems worryingly simple..
这可行,但似乎不必要地复杂。可能有更简单的方法。
This works, but seems unnecessarily complex. There may be an easier way.
这是另一种情况和解决方案:我正在寻找这种情况,其中使用一个查询要选择两个元素。
您可以根据需要修改它以包含 XMLNamespaces。
最初找到的解决方案:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35e75e32-9ffb-4a30-8637-2cc928554763/selecting-multiple -values-from-multiple-rows-of-xml?forum=sqlxml
Here is another situation and solution: I was searching for this situation where there are two elements to be selected using one query.
You can modify this as required to include XMLNamespaces.
Solution originally found at :https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35e75e32-9ffb-4a30-8637-2cc928554763/selecting-multiple-values-from-multiple-rows-of-xml?forum=sqlxml