Hi Tom,

Thank you for your reply.

Actually, We are having one process which will load the xml data into the xml table in first step. And after this loading, xml table will be used for further processing.

So my requirement here is, i want to validate the xmldata exists in the xml table (first step process), and to proceed further to the next step if the data in that xml table is valid. otherwise, i should not move to the next step. (in other words quit from the further process)

I do have .xsd file available for the xml data.
Is there any way that i can validate the xml data by using/comparing with .xsd file.

Hi Tom,

I have gone through the link that you had given.

I tried using the same process, and is working fine if the xml schema size is <32 KB

But there is an error while registering large xml schema (which is >32 KB).

Error report:
ORA-06550: line 4, column 49:
PLS-00172: string literal too long
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Any solution for this?

I read few posts from google saying that to split the xml schema into smaller parts. But is there any solution without spliting the file?

thanks in advance.

Hi Tom,

I managed to register the large XML file using CLOB variable assigned.

But i want to know, is there any error log can be generated or seen if the XML is not valid, i mean when using xmlisvalid function, it returns '0' if the XML is invalid. So is there anyway to find out on which part of the XML file gone wrong. Please help me.

Thanks a lot Tom.

Team,

Here is my query return the output correct when values hardcoded but not with bind variables. can you help us to understand what i am missing here ?

the database version is 21c (21.3)

demo@PDB1> select count(*)
  2  from demo_xml
  3  where xmlexists( '$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
  4      passing object_value as "p" ) ;

  COUNT(*)
----------
         1

demo@PDB1> variable n varchar2(60)
demo@PDB1> exec :n := 'SBELL-2002100912333601PDT';

PL/SQL procedure successfully completed.

demo@PDB1> select count(*)
  2  from demo_xml
  3  where xmlexists( '$p/PurchaseOrder[Reference="{$x1}"]'
  4     passing object_value as "p", :n as "x1" );

  COUNT(*)
----------
         0

demo@PDB1>

Here is my sample XML that got loaded into the "demo_xml" table.

<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
      Redwood Shores
      CA
      94065
      USA</address>
    <telephone>650 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>

SQL> select count(*)
  2      from demo_xml
  3      where xmlexists( '$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
  4          passing xml as "p" ) ;

  COUNT(*)
----------
         1

SQL>
SQL> select count(*)
  2      from demo_xml
  3      where xmlexists( '$p/PurchaseOrder[Reference=$x1]'
  4         passing xml as "p", 'SBELL-2002100912333601PDT' as "x1" );

  COUNT(*)
----------
         1

SQL>
SQL> variable n varchar2(60)
SQL> exec :n := 'SBELL-2002100912333601PDT';

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*)
  2      from demo_xml
  3      where xmlexists( '$p/PurchaseOrder[Reference=$x1]'
  4         passing xml as "p", :n as "x1" );

  COUNT(*)
----------
         1