从Oracle SQL中的Blob提取XML标记之间存在的值 [英] extract value present between XML tags from a blob in Oracle SQL

问题描述

这些数据存在于我的ADMINTXNUNAUTHDATA表中的Blob列("MSG")中

these data is present in a blob COLUMN ("MSG") in my ADMINTXNUNAUTHDATA table

<PayBillerRequestDTO><idCustomer>00000024</idCustomer><idBiller>VODA</idBiller><billerName>ojas yadnik </billerName><billReferenceNumber>111</billReferenceNumber></PayBillerRequestDTO>

我想显示<billReferenceNumber>111</billReferenceNumber>

和其他一些值来自单个查询中的不同表

and some other values coming from different tables in a single query

select mbl.idbiller as BILLNUMBER,
       mbl.billernickname AS BILLERNICKNAME,
       mts.idchanneluser AS USERNAME,
       adm.NUMAMOUNT AS AMOUNT,
       adm.ACCOUNTNO AS ACCOUNTNUM,
       adm.DATINITIATION AS TRANSACTIONDATE,
       adm.codcurr as CURRENCY
  from mstbiller mbl, ADMINTXNUNAUTHDATA adm, mstchanneluser mts
 where MTS.IDCHANNELUSER = '??'
   and adm.idinitiator = mts.iduser 
   and adm.txnid = 'BPA'
   and mbl.idbiller ='?'
   AND dbms_lob.instr(MSG, utl_raw.CAST_TO_RAW('<idBiller>?</idBiller>'), 1, 1) > 0;

推荐答案

我不建议使用INSTR搜索XML标签，因为它对空间敏感，例如，找不到<idBiller>111</idBiller >或<idBiller a="x">111</idBiller> .毕竟它是XML，据认为很容易解析. (不过，没有人提到过awfull语法).

I would not recommend searching for XML tags with INSTR as it is space sensitive, for instance, it wouldn't find <idBiller>111</idBiller > or <idBiller a="x">111</idBiller>. It is XML after all, which is supposedly easy to parse. (Nobody mentioned the awfull syntax, though).

第二，BLOB完全是错误的数据类型.它用于二进制数据.只需等待第一个称为Agüero或Jørgensen的客户. Oracle中XML的适当数据类型是XMLTYPE:

Secondly, BLOB is totally the wrong datatype. It is for binary data. Just wait for the first customer called Agüero or Jørgensen. The appropriate datatype for XML in Oracle is XMLTYPE:

CREATE TABLE admintxnunauthdata (
  accountno NUMBER,
  msg XMLTYPE
) XMLTYPE COLUMN msg STORE AS SECUREFILE BINARY XML (COMPRESS HIGH);

INSERT INTO admintxnunauthdata (accountno, msg) VALUES (1,
 '<PayBillerRequestDTO><idCustomer>00000024</idCustomer><idBiller>VODA</idBiller><billerName>ojas yadnik </billerName><billReferenceNumber>111</billReferenceNumber></PayBillerRequestDTO>');
INSERT INTO admintxnunauthdata (accountno, msg) VALUES (2,
 '<PayBillerRequestDTO><idCustomer>00000025</idCustomer><idBiller>JODA</idBiller><billerName>ojas yadnik </billerName><billReferenceNumber>222</billReferenceNumber></PayBillerRequestDTO>');

然后您可以通过idBiller搜索记录:

You can then search records by idBiller:

SELECT * 
  FROM admintxnunauthdata
 WHERE XMLExists('/PayBillerRequestDTO[idBiller="VODA"]' PASSING msg);

并从XML中提取标签值:

and extract tag values from the XML:

SELECT accountno,
       XMLQuery('/PayBillerRequestDTO/billReferenceNumber/text()' 
         PASSING msg RETURNING CONTENT)
  FROM admintxnunauthdata
 WHERE XMLExists('/PayBillerRequestDTO[idBiller="VODA"]' PASSING msg);

要将XML文本转换为普通数据类型，请使用

To convert the XML text to a normal datatype, use

SELECT accountno,
       XMLCast(XMLQuery('/PayBillerRequestDTO/billReferenceNumber/text()' 
         PASSING msg RETURNING CONTENT) AS NUMBER) AS billreferencenumber
  FROM admintxnunauthdata
 WHERE XMLExists('/PayBillerRequestDTO[idBiller="VODA"]' PASSING msg);

ACCOUNTNO BILLREFERENCENUMBER
        1                 111

如果您不能更改(或更改)表结构，则理论上可以将BLOB即时转换为XMLTYPE，但是我对性能的影响一无所知:

If you cannot change (or let change) the table structure, you could in theory convert the BLOB to XMLTYPE on the fly, but I have no idea about the performance impact:

SELECT accountno,
       XMLCast(XMLQuery('/PayBillerRequestDTO/billReferenceNumber/text()' 
         PASSING msg RETURNING CONTENT) AS NUMBER) AS billreferencenumber
  FROM (
        SELECT accountno, XMLTYPE(msg,1) as msg 
          FROM admintxnunauthdata
       )
 WHERE XMLExists('/PayBillerRequestDTO[idBiller="VODA"]' PASSING msg);

有关从BLOB到XMLTYPE的转换，请参见将oracle blob转换为xml类型

For the conversion from BLOB to XMLTYPE, see convert oracle blob to xml type

这篇关于从Oracle SQL中的Blob提取XML标记之间存在的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！