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?

I am open to add more details and I certainly appreciate negative vote but it would be helpful if down voter can add comment as to why negative vote. — Rachel
– Rachel, Commented Sep 23, 2015 at 23:13
I dont know much about XML columns, but is this acceptable way to store one? <TRNX_ITEM sequence="1" name="ERROR_CODE" value="2009"/> I mean shouldn't there be an end tag /TRNX_ITEM somewhere? — Utsav
– Utsav, Commented Sep 24, 2015 at 2:04

Noel · Accepted Answer · 2015-09-24 07:48:20Z

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.

Collectives™ on Stack Overflow

How to extract nested XMLType Data from Oracle Database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related