SQL Oracle如何在Blob列中提取XML标签内容

Question

有一个XML在类型Blob（In Oracle）中，我需要从该XML访问某个标签。在此之前，我可以以这种方式检索列：

SELECT TRIM(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(my_column_blob, 1024))) as tag_name_xml
FROM my_table

返回就是这样：

在这里我留下XML内容的一部分：

我如何返回CUF或CCT标签的值/内容？ 我希望有更多知识的人的帮助。

提出的解决方案非常优雅，但是就我而言，使用此XML结构访问CUF标签的内容会是什么样？

<?xml version="1.0" encoding="UTF-8"?>
<cteProc xmlns="http://www.example.com" version="3.00">
   <CTe>
      <infCte version="3.00">
         <ide>
            <cUF>15</cUF>
         </ide>
      </infCte>
   </CTe>
</cteProc>

Answer 1

使用xmltype和xmltable：

SELECT x.c
FROM   table_name t
       CROSS JOIN XMLTABLE(
         '/a/b/c'
         PASSING XMLTYPE(t.value, 1)
         COLUMNS
           c VARCHAR2(200) PATH './text()'
       ) x;

或xmlquery：

SELECT XMLQUERY('/a/b/c/text()' PASSING XMLTYPE(value, 1) RETURNING CONTENT) AS c
FROM   table_name

对于示例数据：

CREATE TABLE table_name (value BLOB);

INSERT INTO table_name (value)
VALUES ( UTL_RAW.CAST_TO_RAW( '<a><b><c>something</c></b></a>' ) );

两者都输出：输出：

c

db＆lt;＆gt; fiddle

Answer 2

您可以首先将BLOB数据转换为CLOB类型，然后提取所需的标签。希望以下两个功能可以解决您的问题：

FUNCTION BLOB2CLOB(mBLOB IN BLOB) 
    RETURN CLOB IS
BEGIN
    Declare
        mCLOB   CLOB;
        mDestOffSet     INTEGER;
        mSrcOffSet      INTEGER;
        mBLOBCSID       INTEGER;
        mLangCntx       INTEGER;
        mWarn           INTEGER;
    Begin
        mDestOffSet := 1;
        mSrcOffSet := 1;
        mBLOBCSID := 0;
        mLangCntx := 0;
        mWarn := 0;
        DBMS_LOB.CreateTemporary(mCLOB, TRUE);
        DBMS_LOB.ConvertToClob(mCLOB, mBLOB, DBMS_LOB.GetLength(mBLOB), mDestOffSet, mSrcOffSet, mBLOBCSID, mLangCntx, mWarn);
        RETURN(mCLOB);
    End;
END BLOB2CLOB;
--
FUNCTION Get_SOAP_PartCLOB(mCLOB IN CLOB, mTag IN VARCHAR2) RETURN CLOB 
  IS
BEGIN
    Declare
        wrkCLOB         CLOB;
    myStart       NUMBER(10) := 0;
    myEnd         NUMBER(10) := 0;
    myLength      NUMBER(10) := 0;
    toErase       NUMBER(10) := 0;
    Begin
    DBMS_LOB.CreateTemporary(wrkCLOB, True);
    DBMS_LOB.COPY(wrkCLOB, mCLOB, DBMS_LOB.GETLENGTH(mCLOB));
    --
    myStart := DBMS_LOB.InStr(wrkCLOB, mTag, 1, 1) + Length(mTag) - 1;
    myEnd := DBMS_LOB.InStr(wrkCLOB, SubStr(mTag, 1, 1) || '/' || SubStr(mTag, 2));
    myLength := DBMS_LOB.GetLength(wrkCLOB);
    
    DBMS_LOB.ERASE(wrkCLOB, myStart, 1);
    toErase := myLength - myEnd + 1;

    DBMS_LOB.ERASE(wrkCLOB, toErase, myEnd);
    
    wrkCLOB := REPLACE(wrkCLOB, ' ', '');

        RETURN(wrkCLOB);
    End;
END Get_SOAP_PartCLOB;

您可以这样使用它：

SELECT 
    Get_SOAP_PartCLOB(BLOB2CLOB(YOUR_BLOB_COL), 'your_tag_in_clob') as "TAG_COL_ALIAS"
FROM
    YOUR_TABLE

我使用第二个功能get_soap_partclob（）从某些Web服务的SOAP信封响应中获取一些很大的B64数据。这就是为什么返回类型是clob的原因。您可以将返回的值投放到其他方面，也可以对功能进行自己的调整。