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?

Your XML sample is not well-formed XML. Please edit your post and fix it. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Feb 27, 2020 at 4:27

Yitzhak Khabinsky · Accepted Answer · 2020-02-27 04:43:24Z

2

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

edited Feb 27, 2020 at 4:43

answered Feb 27, 2020 at 4:37

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

richa verma Over a year ago

How will I get all the names and values (in the same order) if I have XML like: <P1> <P2> <P3 name='[1] name1', value='val1'> </P3> <P4 name='[1] name2', value='val2'> </P4> <P3 name='[2] name3', value='val3'> </P3> <P5 name='[1] name4', value='val4'> </P5> <P3 name='[3] name5', value='val5'> </P3> </P2> </p1>

Collectives™ on Stack Overflow

Loop on XML data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related