CODEWITHSUNDEEP
xmloracle-database
DZN
DZN

Reputation: 1553

How to parse attribute values from XML with ORACLE

Here I have a code that extracts rates from an XML :

with tbl as
(
    select
        XMLType(
        '<exchange_rates>
            <rate units="1" code="AUD">2.6</rate>
            <rate units="1" code="THB">0.1</rate>
            <rate units="1" code="BRL">0.9</rate>
            <rate units="1" code="BGN">2.2</rate>
            <rate units="1" code="CAD">2.9</rate>
            <rate units="100" code="CLP">0.5</rate>
        </exchange_rates>'
        ) xml
    from
        t_temp
)
select
    extractValue(value(t), 'rate') result
from
    tbl t,
    table(XMLSequence(t.xml.extract('//rate'))) t;

The result for this code is

RESULT
------
2.6
0.9
2.2
2.9
0.5

It's OK but I'd like to get also attribute values and get a result like this:

Units Code RESULT
----- ---- ------
    1 AUD  2.6
    1 BRL  0.9
    1 BGN  2.2
    1 CAD  2.9
  100 CLP  0.5

Is there a way to do it?

Upvotes: 0

Views: 1546

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Oracle's XML functions take XPath expressions, so you'd use the @attributename syntax to identify attributes. Also, you can use the XMLTABLE function to make the structure a bit easier/clearer.

with tbl as
(
    select
        XMLType(
        '<exchange_rates>
            <rate units="1" code="AUD">2.6</rate>
            <rate units="1" code="THB">0.1</rate>
            <rate units="1" code="BRL">0.9</rate>
            <rate units="1" code="BGN">2.2</rate>
            <rate units="1" code="CAD">2.9</rate>
            <rate units="100" code="CLP">0.5</rate>
        </exchange_rates>'
        ) xmldata
    from
        dual
)
select units, code, rate
from   tbl,
       xmltable('/exchange_rates/rate' 
                      PASSING tbl.xmldata 
                      COLUMNS rate NUMBER PATH '.', 
                              code VARCHAR2(3) PATH './@code', 
                              units NUMBER PATH './@units');

+-------+------+------+
| UNITS | CODE | RATE |
+-------+------+------+
|     1 | AUD  |  2.6 |
|     1 | THB  |  0.1 |
|     1 | BRL  |  0.9 |
|     1 | BGN  |  2.2 |
|     1 | CAD  |  2.9 |
|   100 | CLP  |  0.5 |
+-------+------+------+

Upvotes: 2

Related Questions