4 Replies Latest reply on Aug 14, 2019 5:23 PM by cormaco

    XML Document Shredding

    Gareth S

      Hi,

       

      I am probably missing the pertinent place in the manual(s) so please feel free to point me in the right direction... but from my limited XML experience I am under the impression that with an XSD and a compliant, to that XSD, XML document Oracle could automatically "shred" the document into the tables created when registering the XSD.

       

      So given the following XSD:

       

      <?xml version="1.0" encoding="utf-8"?>
      <xs:schema
        xmlns:xs="http://www.w3.org/2001/XMLSchema"
        xmlns:vcm="http://www.example.com/vcm"
        xmlns:xdb="http://xmlns.oracle.com/xdb"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        elementFormDefault="qualified"
        targetNamespace="http://www.example.com/vcm">
        <xs:element name="PrjAction" xdb:defaultTable="VCM_PRJACTION">
          <xs:annotation>
            <xs:documentation>Root Table for VCM Export</xs:documentation>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:element ref="vcm:Object"/>
            </xs:sequence>
            <xs:attribute name="ActionId" use="required" type="xs:NMTOKEN"/>
            <xs:attribute name="ActionType" use="required" type="xs:NCName"/>
            <xs:attribute name="ObjectFullName" use="required"/>
            <xs:attribute name="ObjectId" use="required" type="xs:integer"/>
          </xs:complexType>
        </xs:element>
        <xs:element name="Object" xdb:defaultTable="VCM_OBJECT">
          <xs:complexType>
            <xs:sequence maxOccurs="unbounded">
              <xs:element ref="vcm:OfferingIO"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="OfferingIO" xdb:defaultTable="VCM_OFFERINGIO">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="offeringId" type="xs:integer"/>
              <xs:element name="offeringName" type="xs:string"/>
              <xs:element name="offeringShortName" type="xs:string"/>
              <xs:element name="offeringStatus" type="xs:string"/>
              <xs:element name="offeringCode" type="xs:string"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      

       

      I then register the schema with:

      DECLARE
        v_schemaurl         CONSTANT  VARCHAR2(30)  :=  'VCM_Schema.xsd';
        v_schemadoc         CONSTANT  BFILE         :=  BFILENAME('&&VCM_XML_DIR.', 'VCM_Schema.xsd');
      BEGIN
        DBMS_XMLSCHEMA.REGISTERSCHEMA(
           schemaurl        =>  v_schemaurl,
           schemadoc        =>  v_schemadoc,
           local            =>  TRUE, 
           gentypes         =>  TRUE,
           genbean          =>  FALSE,
           gentables        =>  TRUE,
           csid             =>  nls_charset_id('al32utf8')
           );
      END;
      /
      

       

      This is successful and I can see the tables VCM_PRJACTION, VCM_OBJECT, VCM_OFFERINGIO as expected with their associated types. I then insert the following XML document:

      <?xml version="1.0" encoding="UTF-8"?>
      <PrjAction ActionId="902O" ActionType="OBJECT" ObjectFullName="VCM Test" ObjectId="11001" xmlns="http://www.example.com/vcm">
        <Object>
          <OfferingIO>
            <offeringId>11001</offeringId>
            <offeringName>Offering Example 01</offeringName>
            <offeringShortName>Offer 01</offeringShortName>
            <offeringStatus>R</offeringStatus>
            <offeringCode>CX44110</offeringCode>
          </OfferingIO>
          <OfferingIO>
            <offeringId>11002</offeringId>
            <offeringName>Offering Example 02</offeringName>
            <offeringShortName>Fees 01</offeringShortName>
            <offeringStatus>R</offeringStatus>
            <offeringCode>CX44111</offeringCode>
          </OfferingIO>
          <OfferingIO>
            <offeringId>32000</offeringId>
            <offeringName>Fee Based</offeringName>
            <offeringShortName>FB</offeringShortName>
            <offeringStatus>P</offeringStatus>
            <offeringCode>FB400</offeringCode>
          </OfferingIO>
        </Object>
      </PrjAction>
      

       

      I then insert as follows and the statement completes with no errors. If I select from VCM_PRJACTION I can see/access the XML but this has not propagated to the child tables as I had hoped.

      SQL> INSERT INTO
        2    vcm_prjaction
        3  VALUES
        4    (XMLTYPE(BFILENAME('&&VCM_XML_DIR.', 'VCM_Sample.xml'),nls_charset_id('AL32UTF8')));
      Enter value for vcm_xml_dir: OCS_XML_DIR
      old   4:   (XMLTYPE(BFILENAME('&&VCM_XML_DIR.', 'VCM_Sample.xml'),nls_charset_id('AL32UTF8')))
      new   4:   (XMLTYPE(BFILENAME('VCM_XML_DIR', 'VCM_Sample.xml'),nls_charset_id('AL32UTF8')))
      
      
      1 row created.
      
      
      SQL> SELECT
        2    COUNT(*)
        3  FROM
        4    vcm_prjaction;
      
      
        COUNT(*)
      ----------
               1
      
      
      SQL>
      SQL> SELECT
        2    COUNT(*)
        3  FROM
        4    vcm_offeringio;
      
      
        COUNT(*)
      ----------
               0
      

       

      So the two questions are:

      1. Can Oracle automatically Shred the document into the child tables?
      2. If so, what am I missing?
      3. If the document is shredded do I still use the XPath type notation with XMLTable to access the data or can I reference the tables directly?

       

      Many thanks in advance,

       

      Gareth.

        • 1. Re: XML Document Shredding
          Gareth S

          Apologies, I should add the target version for this is 11.2.0.4.

           

          Thanks

           

          Gareth.

          • 2. Re: XML Document Shredding
            cormaco

            It does not work that way, the relevant documentation is here:

            XML Schema Storage and Query: Basic

             

            Especially the note here:

            Mapping XML Schema Data Types to SQL Data Types

            Note:

            Do not directly access the SQL data types that are mapped from XML Schema data types during XML schema registration. These SQL types are part of the implementation of Oracle XML DB. They are not exposed for your use.

            Oracle reserves the right to change the implementation at any time, including in a product patch. Such a change by Oracle will have no effect on applications that abide by the XML abstraction, but it might impact applications that directly access these data types.

            So you have to use the SQL/XML functions XMLQUERY and XMLTABLE to retrieve your data in a relational form, regardless of the storage model.

            1 person found this helpful
            • 3. Re: XML Document Shredding
              Gareth S

              Hi,

               

              Thanks for the response and apologies for the delay in responding. With regards to the quoted note, isn't that referring to the PLSQL Types that are created as a result of the gentypes => true? It doesn't seem to be referring to the tables that are created as a result of the gentables => true parameter, at least as I read it.

               

              So what I still don't feel I fully understand is why when gentables => true is used it creates the additional table (in this instance VCM_OFFERINGIO) but then appears to not use it when I insert into the "parent" table VCM_PRJACTION. I understand that I would have to query VCM_PRJACTION using XPath notation, but then when I get to the "<OfferingIO>" level that still appears to be contained in the parent table rather than in the child table.

               

              i.e. I was expecting the query to be a join between VCM_PRJACTION and VCM_OFFERINGIO.

               

              However it seems that you drive everything off VCM_PRJACTION and to get the Offering IO sequence you would pass that XML fragment to another call XMLTable which would do the expansion. That leaves me wondering what the purpose of the auto created table(s) are, why, given the example XSD does VCM_OFFERINGIO get created at all?

               

              Thank you for your patience with an Oracle XMLDB newbie.

               

              Best regards

               

              Gareth.

              • 4. Re: XML Document Shredding
                cormaco

                With regards to the quoted note, isn't that referring to the PLSQL Types that are created as a result of the gentypes => true? It doesn't seem to be referring to the tables that are created as a result of the gentables => true parameter, at least as I read it.

                I don't think so. These have to be SQL Types for the database to work with. Maybe this text here is clearer:

                Storage and Access Infrastructure

                For object-relational storage, XML schema registration creates the appropriate SQL object types for the structured storage of conforming documents.

                I can't explain why the tables contain not the data you expected, I never used object-relational storage.

                One reason object-relational storage exists is because it was the most effective way to store XML-files before the introduction of binary xml storage which is today the default.