How to loop through xml element and get attribute name and value

Question

I'm currently writing a stored procedure for SQL Server 2008 and I am a bit stuck with looping through a xml.

The code is like the following:

    DECLARE    @x1 XML

SELECT @x1 = '<PARAMS>
<PARAMETER NAME="Category 1">
<VALUE>Value 1</VALUE>
</PARAMETER>
<PARAMETER NAME="Category 2">
<VALUE>Value 2</VALUE>
</PARAMETER>
<PARAMETER NAME="Category 3">
<VALUE>Value 3</VALUE>
</PARAMETER>
</PARAMS>'

DECLARE 
@cnt INT, 
@totalcount INT,
@attName VARCHAR(30),
@attValue VARCHAR(30)

SELECT 
@cnt = 1,
@totalcount = @x1.value('count(/PARAMS/PARAMETER)','INT')
PRINT @totalcount;
-- loop
WHILE @cnt <= @totalcount BEGIN
SELECT
@attName =  @x1.value(
'(/PARAMS/PARAMETER/@NAME)[1]',
'VARCHAR(30)'),

@attValue = @x1.value(
'(/PARAMS/PARAMETER/VALUE)[1]',
'VARCHAR(30)')

PRINT 'Attribute Position: ' + CAST(@cnt AS VARCHAR)
PRINT 'Attribute Name: ' + @attName
PRINT 'Attribute Value: ' + @attValue
PRINT ''

SELECT @cnt = @cnt + 1
END

How can i use the @cnt variable to loop through all xml elements?

The output im looking for would be something like this :

Attribute Position: 1
Attribute Name: Category 1
Attribute Value: Value 1

Attribute Position: 2
Attribute Name: Category 2
Attribute Value: Value 2

Attribute Position: 3
Attribute Name: Category 3
Attribute Value: Value 3

marc_s · Accepted Answer · 2014-03-11 09:11:58Z

1

Does this work for you? No RBAR (row-by-agonizing-row) processing and looping necessary - just "shred" the XML into relational rows&columns:

SELECT
    Category = XPar.value('@NAME', 'varchar(20)'),
    NodeValue = XPar.value('(VALUE)[1]', 'varchar(20)')
FROM
    @X1.nodes('/PARAMS/PARAMETER') AS XT(XPar)

Gives an output something like:

Category    NodeValue
------------------------
Category 1  Value 1
Category 2  Value 2
Category 3  Value 3

answered Mar 11, 2014 at 9:11

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

user3405233 Over a year ago

Hi, thanks for the answer. Unfortunately it is required that i have to iterate through the single elements and execute other procedures depending on what was transmitted via the xml. Therefore i would like to store the values inside two variables and do a switch case after each iteration.

Collectives™ on Stack Overflow

How to loop through xml element and get attribute name and value

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