Loop on XML data

Question

In my SQL Server table one column has datatype as XML. It contains data as:

<P1>
<P2>
<P3 name='[1] name1', value='val1'> </P3>
<P3 name='[2] name2', value='val2'> </P3>
<P3 name='[3] name3', value='val3'> </P3>
</P2>
</p1>

I am fetching name1, name2, name3 using query:

select top(1)
    col.value('(/P1[1]/P2[1]/P3/@name)[1]', 'VARCHAR(max)') as q1,
    col.value('(/P1[1]/P2[1]/P3/@name)[2]', 'VARCHAR(max)') as q2,
    col.value('(/P1[1]/P2[1]/P3/@name)[3]', 'VARCHAR(max)') as q3,

    FROM table

How can I loop on this data to get all the names. Something like:

declare @x=1
while @x<4:
begin
    select top(1)
        col.value('(/P1[1]/P2[1]/P3/@name)[@x]', 'VARCHAR(max)') as q@x
        from table
    set @x=@x+1
end

Also how to loop on the same XML to get all the values?

Answer 1

I took the liberty of making your XML well-formed. SQL Server XQuery .nodes() method and CROSS APPLY are delivering what you are after.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata)
VALUES
(N'<P1>
    <P2>
        <P3 name="[1] name1" value="val1">
        </P3>
        <P3 name="[2] name2" value="val2">
        </P3>
        <P3 name="[3] name3" value="val3">
        </P3>
    </P2>
</P1>');
-- DDL and sample data population, end

SELECT tbl.ID
    , c.value('@name','VARCHAR(30)') AS [name]
    , c.value('@value','VARCHAR(30)') AS [value]
FROM @tbl AS tbl
    CROSS APPLY tbl.xmldata.nodes('/P1/P2/P3') AS t(c);

Output

+----+-----------+-------+
| ID |   name    | value |
+----+-----------+-------+
|  1 | [1] name1 | val1  |
|  1 | [2] name2 | val2  |
|  1 | [3] name3 | val3  |
+----+-----------+-------+