在 SQL Server 中使用 value() 从 xml 列中获取多条记录 [英] Getting multiple records from xml column with value() in SQL Server

问题描述

此 SQL 仅返回第一个 Activity 元素.我如何选择它们?如果删除查询中的 [1]，则会收到value() 需要单例"的错误消息.

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

推荐答案

谢谢 Ed，但我找到了一个更简单的版本:

Thanks Ed, but I found an easier version:

SELECT T.C.value('.', 'varchar(100)') as activity
FROM @myDoc.nodes('(/Root/Activities/Activity)') as T(C)

尽管从您的不必要的复杂"示例来看，它似乎简单得令人担忧..

Though from your "unnecessarily complex" example it seems worryingly simple..

这篇关于在 SQL Server 中使用 value() 从 xml 列中获取多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！