How to parse attribute values from XML with ORACLE

Question

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?

Matthew McPeak · Accepted Answer · 2019-09-20 15:14:33Z

2

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 |
+-------+------+------+

answered Sep 20, 2019 at 15:14

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

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

1 Comment

Boneist Over a year ago

As well as XMLTABLE being clearer and easier to work with, EXTRACT and EXTRACTVALUE have been deprecated since (IIRC) 11g!

Collectives™ on Stack Overflow

How to parse attribute values from XML with ORACLE

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related