1 Reply Latest reply: Mar 27, 2014 1:29 AM by odie_63 RSS

    Problem with parsing by XMLTable

    user11093404

      ORACLE version:

      1Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      2PL/SQL Release 11.2.0.3.0 - Production
      3CORE 11.2.0.3.0 Production
      4TNS for Linux: Version 11.2.0.3.0 - Production
      5NLSRTL Version 11.2.0.3.0 - Production

       

      In the following example XMLType is made in SQL and parsing is correct:

       

      declare

      sQuery varchar2(32000) :=

         'select

            a.STORAGEREGISTER_ID

           ,a.STORAGEREGISTER_TYPE_ID

           ,a.CARDID

           ,a.CARDID_INTERNAL_ID

           ,a.CARDID_INTERNAL_TYPEID

           ,case

              when a.STORAGEREGISTER > 0 then

                1

              else

                0

            end Is_STORAGEREGISTER_Exists

          from

            XMLTABLE

            (

              XMLNamespaces(default ''''),

              ''/*''

              PASSING

              (

                xmltype(:xmlRequest)

              )

              COLUMNS

              "STORAGEREGISTER_ID"         number               PATH ''StorageRegister/ID''

             ,"STORAGEREGISTER_TYPE_ID"    number               PATH ''StorageRegister/TypeID''

             ,"CARDID"                     varchar2(19)         PATH ''CardId''

             ,"CARDID_INTERNAL_ID"         number               PATH ''CardId/@internal_id''

             ,"CARDID_INTERNAL_TYPEID"     number               PATH ''CardId/@internal_typeid''

             ,"STORAGEREGISTER"            number               PATH ''count(./StorageRegister)''

            ) a';

      sRequest xmltype := xmltype('<MsgIssuanceCardRq>

        <RqUID DateTime="2001-12-17T09:30:47Z">F50BD467-68D4-FA5D-E045-000000000001</RqUID>

        <ProtocolVersion>text</ProtocolVersion>

        <CustId>

          <SPName>aaaaaa</SPName>

          <CustPermId>0</CustPermId>

        </CustId>

        <StorageRegister>

          <ID/>

          <TypeID>1</TypeID>

        </StorageRegister>

        <CardId internal_typeid="1" internal_id="1">aaaaaaaaaaaaaaaaa</CardId>

        </MsgIssuanceCardRq>');

      nStorageRegisterID number;

      nStorageRegisterTypeID number;

      sCardNo varchar2(19);

      nCardId number;

      nCardTypeId number;

      nIsStorageRegisterExist number;

      begin

        execute immediate sQuery

        into nStorageRegisterID

            ,nStorageRegisterTypeID

            ,sCardNo

            ,nCardId

            ,nCardTypeId

            ,nIsStorageRegisterExist

        using sRequest.GetClobVal();

      end;

      /

       

       

      But in this example XMLType is a parameter and during parse, we've got error:

      ORA-31011: XML parsing failed

      ORA-19202: Error occured in XML processing

      LPX-00601: Invalid token in: '/*/oraxq_defpfx:StorageRegister'

      ORA-06512: at line 40

      Experimentally I've found out, that in this case use of expression (count(./StorageRegister)) in XPATH is unavailable.

       

      declare

      sQuery varchar2(32000) :=

         'select

            a.STORAGEREGISTER_ID

           ,a.STORAGEREGISTER_TYPE_ID

           ,a.CARDID

           ,a.CARDID_INTERNAL_ID

           ,a.CARDID_INTERNAL_TYPEID

           ,case

              when a.STORAGEREGISTER > 0 then

                1

              else

                0

            end Is_STORAGEREGISTER_Exists

          from

            XMLTABLE

            (

              XMLNamespaces(default ''''),

              ''/*''

              PASSING

              (

                :xmlRequest

              )

              COLUMNS

              "STORAGEREGISTER_ID"         number               PATH ''StorageRegister/ID''

             ,"STORAGEREGISTER_TYPE_ID"    number               PATH ''StorageRegister/TypeID''

             ,"CARDID"                     varchar2(19)         PATH ''CardId''

             ,"CARDID_INTERNAL_ID"         number               PATH ''CardId/@internal_id''

             ,"CARDID_INTERNAL_TYPEID"     number               PATH ''CardId/@internal_typeid''

             ,"STORAGEREGISTER"            number               PATH ''count(./StorageRegister)''

            ) a';

      sRequest xmltype;

      nStorageRegisterID number;

      nStorageRegisterTypeID number;

      sCardNo varchar2(19);

      nCardId number;

      nCardTypeId number;

      nIsStorageRegisterExist number;

      begin

        execute immediate sQuery

        into nStorageRegisterID

            ,nStorageRegisterTypeID

            ,sCardNo

            ,nCardId

            ,nCardTypeId

            ,nIsStorageRegisterExist

        using  xmltype('<MsgIssuanceCardRq>

        <RqUID DateTime="2001-12-17T09:30:47Z">F50BD467-68D4-FA5D-E045-000000000001</RqUID>

        <ProtocolVersion>text</ProtocolVersion>

        <CustId>

          <SPName>aaaaaa</SPName>

          <CustPermId>0</CustPermId>

        </CustId>

        <StorageRegister>

          <ID/>

          <TypeID>1</TypeID>

        </StorageRegister>

        <CardId internal_typeid="1" internal_id="1">aaaaaaaaaaaaaaaaa</CardId>

        </MsgIssuanceCardRq>');

      --  dbms_output.put_line(nIsStorageRegisterExist);

      end;

       

      Why do I get this error?

        • 1. Re: Problem with parsing by XMLTable
          odie_63
          Experimentally I've found out, that in this case use of expression (count(./StorageRegister)) in XPATH is unavailable.

          count() function is supported.

          Looks like the empty namespace declaration is causing the problem.

          Remove the XMLNamespaces clause, you don't need it anyway.

           

          Don't use dynamic SQL, you don't need it either. It just complicates thing and hide static compilation errors until the code is actually run.

           

          For the record, you don't have to use count to check if a node exists. You simply use boolean() or exists() which returns a boolean value if the sequence argument is empty, and since it is projected as NUMBER, it is automatically converted to 0 or 1 accordingly, removing the need for the additional CASE statement in the SELECT clause.

           

          , "STORAGEREGISTER"  number PATH 'exists(StorageRegister)'

          , "STORAGEREGISTER"  number PATH 'boolean(StorageRegister)'