CODEWITHSUNDEEP
sql-server-2008t-sqlloopsxquery
user3405233
user3405233

Reputation: 1

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

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

Upvotes: 0

Views: 7560

Answers (1)

marc_s
marc_s

Reputation: 754278

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

Upvotes: 1

Related Questions