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

Question

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

Overture

Answer 1

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

Overture

What if the XML tag appears multiple times and I would like to select as many times as it appears? #26426912 – Bibliology

What is T and what is C – Troposphere

T is an alias for the derived table created by the nodes function. That nodes function returns a piece of xml that includes all the Activity node names + values (and would return any child nodes & values too if there were any). C is a column alias, in this case it breaks up the xml into 'Activity' nodes on each row of that column. – Forbidden

Answer 2

This works, but seems unnecessarily complex. There may be an easier way.

 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 activity.VALUE('(//Activity)[1]','varchar(100)') AS activity
FROM (
        SELECT NewTable.activity.query('.') AS activity
        FROM (SELECT 1 AS col1) AS t
        CROSS APPLY @myDoc.nodes('(/Root/Activities/Activity)') AS NewTable(activity)
     ) AS x

Dogbane

Answer 3

Here is another situation and solution: I was searching for this situation where there are two elements to be selected using one query.

CREATE TABLE #Table1 (ID INT IDENTITY(1,1),XMLDoc XML)

INSERT INTO #Table1 VALUES ('
 <bookstore>
 <name>Bookstore1</name>
 <location>Location1</location>
 <book>
     <title>Titile1</title>
     <price>40</price>
    </book>
 </bookstore>')

 INSERT INTO #Table1 VALUES ('
 <bookstore>
  <name>Bookstore2</name>
 <location>Location2</location>
 <book>
     <title>Titile2</title>
     <price>50</price>
 </book>
</bookstore>')


SELECT ID,
T.c.value('title[1]','varchar(50)') AS 'BookTitile',
T.c.value('price[1]','decimal(18,2)') AS 'Price'
FROM #Table1
CROSS APPLY #Table1.XMLDoc.nodes('/bookstore/book') T(c)

DROP TABLE #Table1

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

Asthenopia