从大xml提取查询失败，字符串文字太长 [英] Extracting from a big xml the query is failing with string literal too long

问题描述

此问题与我无法解析大xml的地方有关. 问题参考链接

This Question is with reference to where I was not able to parse a big xml. Question reference link

当xml的大小很大时，当前为250 kb，解析失败，并且 字符串文字太长.

When the size of the xml is big , currently it is 250 kb the parsing is failing with string literal too long .

请在下面找到解决方案:

Please find the solution below:

推荐答案

解决方案:

1.

    CREATE OR REPLACE FUNCTION  APE9_BLOB_2_CLOB(L_BLOB BLOB) RETURN CLOB IS
        L_CLOB         CLOB;
        L_SRC_OFFSET      NUMBER;
        L_DEST_OFFSET  NUMBER;
        L_BLOB_CSID       NUMBER := DBMS_LOB.DEFAULT_CSID;
        V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
        L_WARNING         NUMBER;
        L_AMOUNT  NUMBER;
      BEGIN
       DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
        L_SRC_OFFSET     := 1;
        L_DEST_OFFSET := 1;
        L_AMOUNT := DBMS_LOB.GETLENGTH(L_BLOB);
        DBMS_LOB.CONVERTTOCLOB(L_CLOB,
                               L_BLOB,
                               L_AMOUNT,
                               L_SRC_OFFSET,
                               L_DEST_OFFSET,
                               1,
                               V_LANG_CONTEXT,
                               L_WARNING);
        RETURN L_CLOB;
      END;
    /

2.

SELECT  XMLTYPE (APE9_BLOB_2_CLOB (XML_LOB )) PROFILE  
FROM APE1_XML_DISTRIB 
WHERE FILE_TYPE='Provider ID list' AND RELEASE_NAME='EPC_TO_PUB'

3.

    SELECT * FROM (
    WITH ET AS(
         SELECT 
                (SELECT  XMLTYPE (APE9_BLOB_2_CLOB (XML_LOB )) PROFILE  FROM APE1_XML_DISTRIB WHERE FILE_TYPE =  'Provider ID list' AND RELEASE_NAME='EPC_TO_PUB' ) AS  XT
          FROM DUAL
           ) 
            SELECT     
                EXT.*
               FROM
               ET,
               XMLTABLE(
               'for $SF in $GRP_2_ID/AuxiliaryType/AuxiliaryObject
                    for $SFItem in $SF/Row
                      return <row> 
                      {
                        $SF
                        ,$SFItem
                      } 
                      </row>'           
                  PASSING ET.XT AS GRP_2_ID
                  COLUMNS
                  ID   Number         PATH 'AuxiliaryObject/@id'  ,
                   PROVIDER_GROUP                 VARCHAR2  (256)       PATH 'AuxiliaryObject/@name' ,  
                   Index_id Number PATH 'Row/Index',              
                  PROVIDER_ID_DESC        VARCHAR2  (256)       PATH 'Row/Provider_ID_description',
                  PROVIDER_ID  VARCHAR2  (64)       PATH 'Row/Provider_ID'
                   ) EXT  
                   ) order by ID,Index_id

这篇关于从大xml提取查询失败，字符串文字太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！