How to get XML element and attribute value in SQL?

Question

I need to get all values from XML either it presents in element or attribute.

Example:

DECLARE @XML = '<root>
                    <row1 attr1="x">1</row1>
                    <row2 attr2="x">2</row2>
                </root>'

Here, my expected output:

Column         Value
---------------------    
row1              1
attr1             x
row2              1
attr2             x

I am able to get elements and attributes values separately by using below queries.

SELECT  
    element.value('local-name(.)', 'VARCHAR(50)'), 
    element.value('.', 'VARCHAR(100)')  
FROM 
    @OldXML.nodes('/root/*') node(element)

SELECT  
    element.value('local-name(.)', 'VARCHAR(50)'), 
    element.value('.', 'VARCHAR(100)')  
FROM 
    @OldXML.nodes('/root/row1/@*') node(element)

SELECT  
    element.value('local-name(.)', 'VARCHAR(50)'), 
    element.value('.', 'VARCHAR(100)')  
FROM 
    @OldXML.nodes('/root/row2/@*') node(element)

But, I need to get the values in a single query.

I surfed many ways, but I can't get a solution for this requirement.

Is there any way to do this?

XML support is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Jul 18 '17 at 11:20

score 0 · Answer 1 · answered Jul 18 '17 at 11:30

Try this UNION ALL

SELECT  
    element.value('local-name(.)', 'VARCHAR(50)'), 
    element.value('.', 'VARCHAR(100)')  
FROM 
    @OldXML.nodes('/root/*') node(element)
UNION ALL
SELECT  
    element.value('local-name(.)', 'VARCHAR(50)'), 
    element.value('.', 'VARCHAR(100)')  
FROM 
    @OldXML.nodes('/root/row1/@*') node(element)
UNION ALL
SELECT  
    element.value('local-name(.)', 'VARCHAR(50)'), 
    element.value('.', 'VARCHAR(100)')  
FROM 
    @OldXML.nodes('/root/row2/@*') node(element)

score 0 · Answer 2 · answered Jul 27 '17 at 14:20

I do not know how deeply oracle supports XQuery. You might transform your XML like this (SQL-Server-syntax):

DECLARE @XML XML = '<root>
                      <row1 attr1="x" attrAdd="test">1</row1>
                      <row2 attr2="y">2</row2>
                      <row3>3</row3>
                    </root>';

SELECT @XML.query
(
N'
    <root>
    {
    for $e in /root/*
    return <element type="element" name="{local-name($e)}" value="{$e/text()}"/>
    }
    {
    for $a in /root/*/@*
    return <element type="attr" parent="{local-name($a/..)}" name="{local-name($a)}" value="{$a}"/>
    }
    </root>
'
);

The result

<root>
  <element type="element" name="row1" value="1" />
  <element type="element" name="row2" value="2" />
  <element type="element" name="row3" value="3" />
  <element type="attr" parent="row1" name="attr1" value="x" />
  <element type="attr" parent="row1" name="attrAdd" value="test" />
  <element type="attr" parent="row2" name="attr2" value="y" />
</root>

Another, nested approach would allow to keep the relationship between element and attributes:

SELECT @XML.query
(
N'
    <root>
    {
        for $e in /root/*
        return 
        <element tag="{local-name($e)}" value="{$e/text()}">
        {
            for $a in $e/@*
            return <attribute tag="{local-name($a)}" value="{$a}"/>
        }
        </element>
    }
    </root>
'
)

The result

<root>
  <element tag="row1" value="1">
    <attribute tag="attr1" value="x" />
    <attribute tag="attrAdd" value="test" />
  </element>
  <element tag="row2" value="2">
    <attribute tag="attr2" value="y" />
  </element>
  <element tag="row3" value="3" />
</root>

Both approaches would allow to read the data in tabular form.

How to get XML element and attribute value in SQL?

2 Answers2