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!

ruudvan · Accepted Answer · 2016-07-06 13:17:23Z

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.

answered Jul 6, 2016 at 13:17

ruudvan

1,3816 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dr_flint Over a year ago

Thank you very much, that works :). After asking my question here, I did some trial and error (out of desperation) and turns out that xpath '/' also works. Is there any difference between '.' and '/'? Anyway, accepting answer :)

ruudvan Over a year ago

Yes. '.' points to the current node and '/' points to the root node. However in this case, since we have already traversed down to the attribute level in the XMLTABLE clause, the PATH clause (xpath) is evaluated after we are already at the attribute level using /@*. So the current node is also the root node of the remaining XML piece. Hope that makes sense.

Mottor · Accepted Answer · 2016-07-06 14:03:42Z

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

Collectives™ on Stack Overflow

getting name and value from xmltype attribute in oracle

Description

Example

What I tried

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Description

Example

What I tried

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related