How to select XML tags value from table in oracle?

Question

I have xml value stored in oracle table as below.

<?xml version="1.0" encoding="UTF-8" standalon="no"?>
<!DOCTYPE TRADE>
<TRADE>
    <SOME_TAGS>
        <TAG>
        ...
        </TAG>
    </SOME_TAGS>
    <QTYS>
        <QTY TYPE="Commission">
            <NUM>0.0000</NUM>
            <ID CLASS="Currency">USD</ID>
        <QTY>
        <QTY TYPE="TransactionFee">
            <NUM>3.0000</NUM>
            <ID CLASS="Currency">USD</ID>
        <QTY>
        <QTY TYPE="TFee">
            <NUM>0.0000</NUM>
            <ID CLASS="Currency">USD</ID>
        <QTY>
        <QTY TYPE="BFee">
            <NUM>1.0000</NUM>
            <ID CLASS="Currency">USD</ID>
        <QTY>       
    </QTYS>
</TRADE>

I want to write select query to get those QTY tags whose NUM value is not 0 and put it to columns. Values in columns depends on non-zero qtys.There can be trades who might have all 4 qtys with non-zero then there will be all values in columns or there can be trades which has few non-zeros qty so add only those who has non-zero values

I tried to get the data using extractvalue function , i can extract value per tag but I want to make it more generic and dont want to write for extractvalue function for each tag and then check if its num value is 0 or not.

Can I use something like xpath ? how to use this?

My expected result is as below

In above example, TradeA has only one set of qty non-zero value so it has selected only one set in select query whereas TradeB has 2 set of non-zero Qty values so it has added values in that many columns and so on.

Answer 1

You can use XMLTABLE to get the values as rows and then PIVOT to transpose them to columns:

SELECT type1, type1_amount, type1_ccy,
       type2, type2_amount, type2_ccy,
       type3, type3_amount, type3_ccy,
       type4, type4_amount, type4_ccy
FROM   (
         SELECT t.ROWID AS id,
                x.type,
                x.amount,
                x.currency,
                ROW_NUMBER() OVER (PARTITION BY t.ROWID ORDER BY x.id) AS rn
         FROM   table_name t
                CROSS APPLY XMLTABLE(
                  '/TRADE/QTYS/QTY'
                  PASSING XMLTYPE(t.xml)
                  COLUMNS
                    id       FOR ORDINALITY,
                    type     VARCHAR2(50) PATH './@TYPE',
                    amount   NUMBER       PATH './NUM',
                    currency VARCHAR2(3)  PATH './ID[@CLASS="Currency"]'
                ) x
         WHERE  amount > 0
       )
       PIVOT (
         MAX(type),
         MAX(amount) AS amount,
         MAX(currency) AS ccy
         FOR rn IN (
           1 AS type1,
           2 AS type2,
           3 AS type3,
           4 AS type4
         )
       )

Which, for the sample data (assuming you have valid XML data including having closing tags to match all the opening tags):

CREATE TABLE table_name (xml) AS
SELECT EMPTY_CLOB() || '<?xml version="1.0" encoding="UTF-8" standalon="no"?>
<TRADE>
    <SOME_TAGS><TAG>...</TAG></SOME_TAGS>
    <QTYS>
        <QTY TYPE="Commission"><NUM>1.3340</NUM><ID CLASS="Currency">USD</ID></QTY>
        <QTY TYPE="TransactionFee"><NUM>0.0000</NUM><ID CLASS="Currency">USD</ID></QTY>
        <QTY TYPE="TFee"><NUM>0.0000</NUM><ID CLASS="Currency">USD</ID></QTY>
        <QTY TYPE="BFee"><NUM>0.0000</NUM><ID CLASS="Currency">USD</ID></QTY>       
    </QTYS>
</TRADE>' FROM DUAL UNION ALL
SELECT EMPTY_CLOB() || '<?xml version="1.0" encoding="UTF-8" standalon="no"?>
<TRADE>
    <SOME_TAGS><TAG>...</TAG></SOME_TAGS>
    <QTYS>
        <QTY TYPE="Commission"><NUM>3.4000</NUM><ID CLASS="Currency">USD</ID></QTY>
        <QTY TYPE="TransactionFee"><NUM>0.0000</NUM><ID CLASS="Currency">USD</ID></QTY>
        <QTY TYPE="TFee"><NUM>3.4000</NUM><ID CLASS="Currency">HKD</ID></QTY>
        <QTY TYPE="BFee"><NUM>0.0000</NUM><ID CLASS="Currency">USD</ID></QTY>       
    </QTYS>
</TRADE>' FROM DUAL UNION ALL
SELECT EMPTY_CLOB() || '<?xml version="1.0" encoding="UTF-8" standalon="no"?>
<TRADE>
    <SOME_TAGS><TAG>...</TAG></SOME_TAGS>
    <QTYS>
        <QTY TYPE="Commission"><NUM>3.3000</NUM><ID CLASS="Currency">USD</ID></QTY>
        <QTY TYPE="TransactionFee"><NUM>2.0000</NUM><ID CLASS="Currency">USD</ID></QTY>
        <QTY TYPE="TFee"><NUM>0.0000</NUM><ID CLASS="Currency">HKD</ID></QTY>
        <QTY TYPE="BFee"><NUM>3.4000</NUM><ID CLASS="Currency">USD</ID></QTY>       
    </QTYS>
</TRADE>' FROM DUAL;

Outputs:

TYPE1	TYPE1_AMOUNT	TYPE1_CCY	TYPE2	TYPE2_AMOUNT	TYPE2_CCY	TYPE3	TYPE3_AMOUNT	TYPE3_CCY	TYPE4	TYPE4_AMOUNT	TYPE4_CCY
Commission	1.334	USD	null	null	null	null	null	null	null	null	null
Commission	3.4	USD	TFee	3.4	HKD	null	null	null	null	null	null
Commission	3.3	USD	TransactionFee	2	USD	BFee	3.4	USD	null	null	null

fiddle