dr_flint dr_flint - 5 months ago 29
SQL Question

getting name and value from xmltype attribute in oracle

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

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.