Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
310 views
in Technique[技术] by (71.8m points)

xquery - Getting multiple records from xml column with value() in SQL Server

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)' )
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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..


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...