How to extract nested XMLType Data from Oracle Database?

Question

I have following xml data stored in xmltype column in database.

<TRNXS_DTL transactionSeq="1">
  <TRNX_ITEM sequence="1" name="ERROR_CODE" value="2009"/>
  <TRNX_ITEM sequence="2" name="ERROR_DESC" value="Data Not Found for given record id."/>
</TRNXS_DTL>

Now I tried querying database using extract as under:

  SELECT SRC_DSC_TXT as "TXT",   
  TYPE_CODE as "Code",   
  PID as "Participant ID",   
  SYS_ID as "System",   
  CHANEL as "Channel",   
  to_char(crt_ts,'MM/DD/YYYY HH24:MI:SS') as "Timestamp (MST)",   
  extract(TRX_DATA, '/TRNXS_DTL/TRNX_ITEM/text()') as "DETAILS"
  FROM PARTICIPANT_DATA,   
  WHERE SRC_VAL_TXT   =TYPE_CD;

But when i run above query i get null under Details column, any thoughts?

Answer 1

The tag in your XML doesn't have any text. It has three attributes: sequence, name and value. So, your query is returning NULL.

Consider the following example.

create table myt(
type_code number,
trx_data xmltype
);

insert into myt values(101,
'<TRNXS_DTL transactionSeq="1">
  <TRNX_ITEM sequence="1" name="ERROR_CODE" value="2009"/>
  <TRNX_ITEM sequence="2" name="ERROR_DESC" value="Data Not Found for given record id."/>
</TRNXS_DTL>'
);

insert into myt values(102,
'<TRNXS_DTL transactionSeq="1">
  <TRNX_ITEM sequence="1" name="ERROR_CODE" value="2010">NNN</TRNX_ITEM>
  <TRNX_ITEM sequence="2" name="ERROR_DESC" value="Data Found for given record id.">OOO</TRNX_ITEM>
</TRNXS_DTL>'
);

SQL> select  * from myt;       

 TYPE_CODE TRX_DATA
---------- -------------------------------------------------------------------------------------------------------------
       101 <TRNXS_DTL transactionSeq="1">
             <TRNX_ITEM sequence="1" name="ERROR_CODE" value="2009"/>
             <TRNX_ITEM sequence="2" name="ERROR_DESC" value="Data Not Found for given record id."/>
           </TRNXS_DTL>

       102 <TRNXS_DTL transactionSeq="1">
             <TRNX_ITEM sequence="1" name="ERROR_CODE" value="2010">NNN</TRNX_ITEM>
             <TRNX_ITEM sequence="2" name="ERROR_DESC" value="Data Found for given record id.">OOO</TRNX_ITEM>
           </TRNXS_DTL>

for type_code 102 has NNN and OOO as text.

SQL> select type_code,
extract(trx_data, '/TRNXS_DTL/TRNX_ITEM/text()').getStringVal() as "DETAILS"
from myt; 

 TYPE_CODE DETAILS
---------- -------------
       101
       102 NNNOOO

You can see that, it concatenates the text from both the tags.

In case, you are after the attribute values, you can use @ to specify the attribute.

SQL> select type_code,
extract(trx_data, '/TRNXS_DTL/TRNX_ITEM/@value').getStringVal() as "DETAILS"
from myt; 

 TYPE_CODE DETAILS
---------- ------------------------------------------
       101 2009Data Not Found for given record id.
       102 2010Data Found for given record id.

Again, as you can see, the text is concatenated.

If you want them in separate columns, you need to give a predicate to conditionally restrict the node that is selected.

SQL> select type_code,
extract(trx_data, '/TRNXS_DTL/TRNX_ITEM[@sequence="1"]/text()').getStringVal() as "DETAILS",
extract(trx_data, '/TRNXS_DTL/TRNX_ITEM[@sequence="2"]/text()').getStringVal() as "DETAILS2"
from myt;

 TYPE_CODE DETAILS                  DETAILS2
---------- ------------------------ --------------
       101
       102 NNN                      OOO


SQL> select type_code,
extract(trx_data, '/TRNXS_DTL/TRNX_ITEM[@sequence="1"]/@value').getStringVal() as "DETAILS",
extract(trx_data, '/TRNXS_DTL/TRNX_ITEM[@sequence="2"]/@value').getStringVal() as "DETAILS2"
from myt;

 TYPE_CODE DETAILS                  DETAILS2
---------- ------------------------ ------------------------------------------
       101 2009                     Data Not Found for given record id.
       102 2010                     Data Found for given record id.