Iterate Over Many XML Attributes In SQL

Question

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!

Answer 1

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