[SOLVED] Iterate Over Many XML Attributes In SQL

Issue

I have a XML file that has a series of attributes. The attributes look something like the list below:

<Summary>
  <MyAttributes AT001="ABC" AT002="123" AT003="456" AT004="DEF" ... />
</Summary>

I need to iterate over the attributes and add them into a SQL table that looks something like this:

Name Value
AT001 ABC
AT002 123
AT003 456
AT004 DEF

Because the attribute list isn’t fixed, I need to iterate over all the attributes to ensure each attribute gets added.

I typically can figure out how to do things in SQL, but this one has me stumped!

Solution

It is not clear what SQL you are using.

Here is how to do it in MS SQL Server by using its T-SQL and XQuery methods.

SQL

DECLARE @xml XML =
N'<Summary>
  <MyAttributes AT001="ABC" AT002="123" AT003="456" AT004="DEF" />
</Summary>';

SELECT c.value('local-name(.)', 'VARCHAR(30)') AS attr_name
    , c.value('.', 'VARCHAR(30)') AS attr_value
FROM @xml.nodes('/Summary/MyAttributes/@*') AS t(c);

Output

+-----------+------------+
| attr_name | attr_value |
+-----------+------------+
| AT001     | ABC        |
| AT002     | 123        |
| AT003     | 456        |
| AT004     | DEF        |
+-----------+------------+

Answered By – Yitzhak Khabinsky

Answer Checked By – Katrina (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.