Как извлечь вложенные данные XMLType из базы данных Oracle?

У меня есть следующие данные xml, хранящиеся в столбце xmltype в базе данных.

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

Теперь я попытался запросить базу данных с помощью извлечения, как показано ниже:

  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;

Но когда я запускаю вышеуказанный запрос, я получаю null в столбце «Подробности», какие-нибудь мысли?


oracle xmltype
person Rachel    schedule 23.09.2015    source источник
comment
Я готов добавить больше деталей, и я, безусловно, ценю отрицательное голосование, но было бы полезно, если бы голосующий против мог добавить комментарий о том, почему отрицательное голосование.   -  person Rachel    schedule 24.09.2015
comment
так что я могу обновить вопрос соответственно.   -  person Rachel    schedule 24.09.2015
comment
Я не очень разбираюсь в столбцах XML, но приемлем ли такой способ их хранения? <TRNX_ITEM sequence="1" name="ERROR_CODE" value="2009"/> Я имею в виду, что где-то должен быть конечный тег /TRNX_ITEM?   -  person Utsav    schedule 24.09.2015

Ответы (1)


arrow_upward
1
arrow_downward

Тег в вашем XML не имеет текста. Он имеет три атрибута: последовательность, имя и значение. Итак, ваш запрос возвращает NULL.

Рассмотрим следующий пример.

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>

для type_code 102 в качестве текста используются NNN и ООО.

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

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

Вы можете видеть, что он объединяет текст из обоих тегов.

В случае, если вам нужны значения атрибута, вы можете использовать @ для указания атрибута.

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.

Опять же, как видите, текст объединен.

Если вы хотите, чтобы они находились в отдельных столбцах, вам нужно указать предикат для условного ограничения выбранного узла.

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.
person Noel    schedule 24.09.2015