getting name and value from xmltype attribute in oracle

Question

Description

Hello, I have a problem with extracting attribute names and values from XMLType value in Oracle. Basically, I have a table, let's say TableA, which has a XMLType column, let's call it TableA_config. Values in TableA_config have structure like this: <TableAConfig someAttribute1="value1" someAttribute2="value2" someAttribute3="value3" />. The number of attributes and their names may vary and are not known beforehand. What I need to do is (for each row) create new XMLElement called TableAConfigList, which contains XMLElements called TableAConfig and each of those has two attributes: name and value. Now, number of TableAConfig nodes must be equal to number of attributes in TableA_config column, and each holds name of corresponding attribute in name attribute and its value in value attribute.

Example

From:

<TableAConfig someAttribute1="value1" someAttribute2="value2" someAttribute3="value3" />

I should get:

<TableAConfigList>
    <TableAConfig name="someAttribute1" value="value1"/>
    <TableAConfig name="someAttribute2" value="value2"/>
    <TableAConfig name="someAttribute3" value="value3"/>
</TableAConfigList>

What I tried

I came up with idea to create a XMLTable from TableA_config column value and in it create two columns, which I can later select. It looks like this (it is a fragment of a bigger query):

SELECT XMLElement("TableAConfigList",
    (SELECT
        XMLAgg(
           XMLElement("TableAConfig",
                XMLAttributes(
                    tmp."attr_name" as "name",
                    tmp."attr_text" as "value"
                )
            )
        ) from XMLTable('/TableAConfig/@*'
            passing TableA.TableA_config
            columns
               "attr_name" varchar(30) path 'name()',
               "attr_text" varchar(30) path 'text()'
        ) tmp
    )
) from dual

But now i get:

<TableAConfigList>
    <TableAConfig name="someAttribute1"></TableAConfig>
    <TableAConfig name="someAttribute2"></TableAConfig>
    <TableAConfig name="someAttribute3"></TableAConfig>
</TableAConfigList>

There is no value. However, if I remove name from XMLAttributes it shows up. From:

SELECT XMLElement("TableAConfigList",
    (SELECT
        XMLAgg(
           XMLElement("TableAConfig",
                XMLAttributes(
                    tmp."attr_text" as "value"
                )
            )
        ) from XMLTable('/TableAConfig/@*'
            passing TableA.TableA_config
            columns
               "attr_name" varchar(30) path 'name()',
               "attr_text" varchar(30) path 'text()'
        ) tmp
    )
) from dual

I get:

<TableAConfigList>
    <TableAConfig value="value1"></TableAConfig>
    <TableAConfig value="value2"></TableAConfig>
    <TableAConfig value="value3"></TableAConfig>
</TableAConfigList>

I thought that maybe, for some reason, there can only be one attribute created this way, but if I add a new one by hardcoding it, it shows up in result, like this:

SELECT XMLElement("TableAConfigList",
    (SELECT
        XMLAgg(
           XMLElement("TableAConfig",
                XMLAttributes(
                    tmp."attr_text" as "value",
                    'testValue' as "testAttribute"
                )
            )
        ) from XMLTable('/TableAConfig/@*'
            passing TableA.TableA_config
            columns
               "attr_name" varchar(30) path 'name()',
               "attr_text" varchar(30) path 'text()'
        ) tmp
    )
) from dual

Result:

<TableAConfigList>
    <TableAConfig value="value1" testAttribute="testValue"></TableAConfig>
    <TableAConfig value="value2" testAttribute="testValue"></TableAConfig>
    <TableAConfig value="value3" testAttribute="testValue"></TableAConfig>
</TableAConfigList>

Putting in XMLAttributes both columns and hardcoded one gives me name and testAttribute, but no value.

Could someone tell me is it because I miss something terribly obvious, is it a bug or am I doing it completely wrong. I am pretty new to Oracle and PL/SQL and could really appreciate your help. Thanks!

Answer 1

You were almost there with your first attempt. While evaluating the XPath, when you are inside the attribute list with /TableAConfig/@*, you don't need text() to get the value of the attribute inside it. You are already at the attribute level so using just a "dot" . for current node would be sufficient.

So try something like this -

SELECT XMLElement("TableAConfigList",
    (SELECT
        XMLAgg(
           XMLElement("TableAConfig",
                XMLAttributes(
                    tmp."attr_name" as "name",
                    tmp."attr_text" as "value"
                )
            )
        ) from XMLTable('/TableAConfig/@*'
            passing TableA.TableA_config
            columns
               "attr_name" varchar(30) path 'name()',
               "attr_text" varchar(30) path '.'
        ) tmp
    )
) from dual

The only difference from your first attempt is the xpath of the value attribute.

Answer 2

You can use XMLTRANSFORM too:

select xmltransform(
       xmltype('<TableAConfig someAttribute1="value1" someAttribute2="value2" someAttribute3="value3" />'),
       xmltype('<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
        <xsl:for-each select="TableAConfig">
<TableAConfigList>
            <xsl:for-each select="@*">
                <xsl:element name="TableAConfig" >
                    <xsl:attribute name="name">
                        <xsl:value-of select="name()"/>
                    </xsl:attribute>
                    <xsl:attribute name="value">
                        <xsl:value-of select="."/>
                    </xsl:attribute>
                </xsl:element>
            </xsl:for-each>
</TableAConfigList>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>'))
from dual