9 Replies Latest reply on Oct 16, 2015 4:32 PM by odie_63

    Loading XML data into an XML schema

    3019429

      I've been given an XML data file and an .xsd file from an external third party company with the requirement to load the XML data into an Oracle table/tables. Using the .xsd file with DBMS_XMLSCHEMA.REGISTERSCHEMA I've created an XML schema, but I'm now at a loss as to how I actually load the XML data file into this XML schema. I thought that the purpose of creating an XML schema was to provide a structure/framework into which the data could be loaded and automatically organised in a logical fashion whilst maintaining the relationships between the elements, i.e. each element of the data file would be loaded into its corresponding table within the XML schema, but despite much searching I haven't been able to find how to do this in any of the relevant Oracle documentation.

       

      Can anybody help?

        • 1. Re: Loading XML data into an XML schema
          3019429

          I think I've figured out how to load the data file into the XML schema using the following code:

           

          DECLARE

               v_return BOOLEAN;

              

            BEGIN

               v_return := DBMS_XDB.CREATERESOURCE(

                              abspath => 'IR_XML_SCHEMA',

                              data    => BFILENAME('LANDING_AREA', 'data.xml')

                              );

               COMMIT;

              

            END;

           

          I say 'think' because the PL/SQL runs successfully, but I haven't yet managed to query the data so I don't know for sure that it's in there.

           

          This is the data that I've tried to load (it's just a single row):

           

          <?xml version='1.0' encoding='utf-8'?>

          <ReportDetails>

             <ExtractVolumes>

                <TotalEntries>7777</TotalEntries>

                <TotalBanks>7777</TotalBanks>

                <TotalIVAs>7777</TotalIVAs>

                <NewBanks>77</NewBanks>

                <TotalDros>77</TotalDros>

             </ExtractVolumes>

             <Disclaimer>Some text </Disclaimer>

             <ReportRequest>

                <ExtractDate>01/09/2015</ExtractDate>

                <CaseNoReportRequest>123456</CaseNoReportRequest>

                <IndividualDetailsText>Individual Details</IndividualDetailsText>

                <IndividualDetails>

                   <CaseNoIndividual>123456</CaseNoIndividual>

                   <Title>MR</Title>

                   <Gender>Male</Gender>

                   <FirstName>TERRY</FirstName>

                   <Surname>TIBBS</Surname>

                   <Occupation>No Occupation Found</Occupation>

                   <DateofBirth>13/07/1974</DateofBirth>

                   <LastKnownAddress>Some text</LastKnownAddress>

                   <LastKnownPostCode>No Last Known PostCode Found</LastKnownPostCode>

                   <OtherNames>No OtherNames Found</OtherNames>

                </IndividualDetails>

                <CaseDetailsText>Some text</CaseDetailsText>

                <CaseDetails>

                   <CaseNoCase>123456</CaseNoCase>

                   <CaseName>TERRY TIBBS</CaseName>

                   <Court>Some text</Court>

                   <CaseType>Some text</CaseType>

                   <CourtNumber>7890</CourtNumber>

                   <CaseYear>1995</CaseYear>

                   <StartDate>05/01/1995</StartDate>

                   <Status>Some text</Status>

                   <CaseDescription>TERRY TIBBS</CaseDescription>

                   <TradingNames>

                      <TradingName>CHAMELEON</TradingName>

                      <TradingAddress></TradingAddress>

                      <TradingName>COMPAQ</TradingName>

                      <TradingAddress></TradingAddress>

                   </TradingNames>

                </CaseDetails>

                <InsolvencyPractitionerText>Insolvency Practitioner Contact Details</InsolvencyPractitionerText>

                <IP>

                   <CaseNoIP>123456</CaseNoIP>

                   <MainIP>JOHN DISNEY</MainIP>

                   <MainIPFirm>Some text</MainIPFirm>

                   <MainIPFirmAddress>Some text</MainIPFirmAddress>

                   <MainIPFirmPostCode>VWG 118Y</MainIPFirmPostCode>

                   <MainIPFirmTelephone>01234 567890</MainIPFirmTelephone>

                </IP>

                <InsolvencyContactText>Insolvency Service Contact Details</InsolvencyContactText>

                <InsolvencyContact>

                <CaseNoContact>67001660</CaseNoContact>

                <InsolvencyServiceOffice>Stockton</InsolvencyServiceOffice>

                <Contact>Enquiry Desk</Contact>

                <ContactAddress>Civic Centre, Barras Bridge, NEWCASTLE UPON TYNE, United Kingdom</ContactAddress>

                <ContactPostCode>NE1 8QH</ContactPostCode>

                <ContactTelephone>0191 260 4600</ContactTelephone

                </InsolvencyContact>

             </ReportRequest>

          </ReportDetails>

           

           

          Here is the list of tables created within the XML schema:

           

          BIN$Ig7xWK0cDH7gUwMLAQpmxw==$0
          BIN$IiKeIhw4TRLgUwMLAQpn/g==$0
          BankruptcyRestric541_TAB
          CaseDetails517_TAB
          DebtReliefRestric528_TAB
          ExtractVolumes570_TAB
          IP497_TAB
          IndividualDetails556_TAB
          InsolvencyContact488_TAB
          OtherNames544_TAB
          PreviousIBRO533_TAB
          PreviousIDRRO523_TAB
          ReportDetails573_TAB
          ReportRequest562_TAB
          TradingNames503_TAB

           

          Given the structure of my data, I think that the 'ReportDetails573_TAB' table is the top-level table. If I look at the data held for it within user_tab_cols, I see this:

           

            

          COLUMN_NAMEDATA_TYPE
          ACLOIDRAW
          OWNERIDRAW
          SYS_NC_OID$RAW
          SYS_NC_ROWINFO$XMLTYPE
          XMLEXTRAXMLTYPEEXTRA
          SYS_NC00004$XMLTYPEPI
          SYS_NC00005$XMLTYPEPI
          XMLDATAReportDetails571_T
          SYS_NC00007$XDB$RAW_LIST_T
          SYS_NC00008$XDB$RAW_LIST_T
          SYS_NC00009$NUMBER
          SYS_NC00010$NUMBER
          SYS_NC00011$NUMBER
          SYS_NC00012$NUMBER
          SYS_NC00013$NUMBER
          SYS_NC00014$VARCHAR2
          SYS_NC00015$ReportRequest572_COLL
          SYS_NC0001500016$RAW

           

          However if I try to query the data using this SQL:

          SELECT *

          FROM "ReportDetails573_TAB" rd,

          TABLE(rd.xmldata."ReportRequest")

           

          I get numerous errors about missing objects, e.g. SCHEMA_NAME.INDIVIDUALDETAILS555_T does not exist, SCHEMA_NAME.BANKRUPTCYRESTRICTI540_T does not exist (where SCHEMA_NAME is the name of the schema that I was logged on as when I created the XML schema.

           

          Can anybody help to point me in the right direction?

          • 2. Re: Loading XML data into an XML schema
            odie_63

            I thought that the purpose of creating an XML schema was to provide a structure/framework into which the data could be loaded and automatically organised in a logical fashion whilst maintaining the relationships between the elements, i.e. each element of the data file would be loaded into its corresponding table within the XML schema

             

            You're referring to object-relational storage of XML data.

            When you register an XML schema for OR storage, Oracle creates a set of object and collection types that mirror the structure defined in the schema.

            It can also optionally create a default XMLType table to hold documents conforming to that schema.

             

            When you load an XML document into a schema-based (OR) XMLType table (or column), Oracle automatically parses it and store the data in their dedicated object instances and collections.

             

            To access the data, you then have to use XQuery through the XMLTABLE, XMLQUERY and XMLEXISTS operators.

            Querying object tables and referencing types created during registration is not supported.

             

            See this article for an overview of what you can do with OR storage :

            https://odieweblog.wordpress.com/2011/11/23/oracle-xml-db-a-practical-example/

            • 3. Re: Loading XML data into an XML schema
              3019429

              Thanks for your response. I might be getting confused, but this guy here seems to be doing exactly what I'd like to do:

               

              http://www.oracle-developer.net/display.php?id=416

               

              The only difference that I can see is that his XML schema definition is much simpler than mine.

              • 4. Re: Loading XML data into an XML schema
                odie_63

                3019429 wrote:

                 

                Thanks for your response. I might be getting confused, but this guy here seems to be doing exactly what I'd like to do:

                 

                http://www.oracle-developer.net/display.php?id=416

                Yes, that's another nice introduction to XML DB features, but it was written back in 2006.

                 

                - extract, extractvalue, xmlsequence etc. are all deprecated now

                - accessing data directly by querying the underlying object model is not supported

                - you don't actually need to create any resource in the XML DB repository

                 

                If you need more help, please post the schema and explain what kind of typical queries you'll need to perform.

                1 person found this helpful
                • 5. Re: Loading XML data into an XML schema
                  3019429

                  Here's the schema definition:

                   

                  <?xml version="1.0" encoding="UTF-8"?>

                  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">

                      <xs:element name="ReportDetails">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="ExtractVolumes"/>

                                  <xs:element ref="Disclaimer"/>

                                  <xs:element ref="ReportRequest" maxOccurs="unbounded"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="ExtractVolumes">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="TotalEntries"/>

                                  <xs:element ref="TotalBanks"/>

                                  <xs:element ref="TotalIVAs"/>

                                  <xs:element ref="NewBanks"/>

                                  <xs:element ref="TotalDros"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="TotalEntries" type="xs:integer"/>

                      <xs:element name="TotalBanks" type="xs:integer"/>

                      <xs:element name="TotalIVAs" type="xs:integer"/>

                      <xs:element name="NewBanks" type="xs:integer"/>

                      <xs:element name="TotalDros" type="xs:integer"/>

                      <xs:element name="Disclaimer" type="xs:string"/>

                      <xs:element name="ReportRequest">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="ExtractDate"/>

                                  <xs:element ref="CaseNoReportRequest"/>

                                  <xs:element ref="IndividualDetailsText"/>

                                  <xs:element ref="IndividualDetails"/>

                                  <xs:choice minOccurs="0">

                                      <xs:element ref="BankruptcyRestrictionsDetails"/>

                                      <xs:element ref="DebtReliefRestrictionsDetails"/>

                                  </xs:choice>

                                  <xs:sequence minOccurs="0">

                                      <xs:element ref="CaseDetailsText"/>

                                      <xs:element ref="CaseDetails"/>

                                  </xs:sequence>

                                  <xs:sequence minOccurs="0" maxOccurs="unbounded">

                                      <xs:element ref="InsolvencyPractitionerText"/>

                                      <xs:element ref="IP"/>

                                  </xs:sequence>

                                  <xs:element ref="InsolvencyContactText"/>

                                  <xs:element ref="InsolvencyContact"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="ExtractDate" type="xs:string"/>

                      <xs:element name="CaseNoReportRequest" type="xs:integer"/>

                      <xs:element name="IndividualDetailsText" type="xs:string"/>

                      <xs:element name="IndividualDetails">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="CaseNoIndividual"/>

                                  <xs:element ref="Title"/>

                                  <xs:element ref="Gender"/>

                                  <xs:element ref="FirstName"/>

                                  <xs:element ref="Surname"/>

                                  <xs:element ref="Occupation"/>

                                  <xs:element ref="DateofBirth"/>

                                  <xs:element ref="DeceasedDate" minOccurs="0"/>

                                  <xs:element ref="LastKnownAddress"/>

                                  <xs:element ref="LastKnownPostCode"/>

                                  <xs:element ref="OtherNames"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="CaseNoIndividual" type="xs:integer"/>

                      <xs:element name="Title" type="xs:string"/>

                      <xs:element name="Gender" type="xs:string"/>

                      <xs:element name="FirstName" type="xs:string"/>

                      <xs:element name="Surname" type="xs:string"/>

                      <xs:element name="Occupation" type="xs:string"/>

                      <xs:element name="DateofBirth" type="xs:string"/>

                      <xs:element name="DeceasedDate" type="xs:string"/>

                      <xs:element name="LastKnownAddress" type="xs:string"/>

                      <xs:element name="LastKnownPostCode" type="xs:string"/>

                      <xs:element name="OtherNames">

                          <xs:complexType mixed="true">

                              <xs:sequence>

                                  <xs:element ref="OtherName" minOccurs="0" maxOccurs="unbounded"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="OtherName" type="xs:string"/>

                      <xs:element name="BankruptcyRestrictionsDetails">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="RestrictionsType" minOccurs="0"/>

                                  <xs:element ref="RestrictionsStartDate" minOccurs="0"/>

                                  <xs:element ref="RestrictionsEndDate" minOccurs="0"/>

                                  <xs:element ref="RestrictionsCourt" minOccurs="0"/>

                                  <xs:element ref="RestrictionsCourtNo" minOccurs="0"/>

                                  <xs:element ref="RestrictionsCaseYear" minOccurs="0"/>

                                  <xs:element ref="PreviousIBRO" minOccurs="0"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="RestrictionsType" type="xs:string"/>

                      <xs:element name="RestrictionsStartDate" type="xs:string"/>

                      <xs:element name="RestrictionsEndDate" type="xs:string"/>

                      <xs:element name="RestrictionsCourt" type="xs:string"/>

                      <xs:element name="RestrictionsCourtNo" type="xs:int"/>

                      <xs:element name="RestrictionsCaseYear" type="xs:int"/>

                      <xs:element name="PreviousIBRO">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="PreviousIBRONote"/>

                                  <xs:element ref="PreviousIBROStartDate"/>

                                  <xs:element ref="PreviousIBROEndDate"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="PreviousIBRONote" type="xs:string"/>

                      <xs:element name="PreviousIBROStartDate" type="xs:string"/>

                      <xs:element name="PreviousIBROEndDate" type="xs:string"/>

                      <xs:element name="DebtReliefRestrictionsDetails">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="DRORestrictionsType" minOccurs="0"/>

                                  <xs:element ref="DRORestrictionsStartDate" minOccurs="0"/>

                                  <xs:element ref="DRORestrictionsEndDate" minOccurs="0"/>

                                  <xs:element ref="PreviousIDRRO" minOccurs="0"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="DRORestrictionsType" type="xs:string"/>

                      <xs:element name="DRORestrictionsStartDate" type="xs:string"/>

                      <xs:element name="DRORestrictionsEndDate" type="xs:string"/>

                      <xs:element name="PreviousIDRRO">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="PreviousIDRRONote"/>

                                  <xs:element ref="PreviousIDRROStartDate"/>

                                  <xs:element ref="PreviousIDRROEndDate"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="PreviousIDRRONote" type="xs:string"/>

                      <xs:element name="PreviousIDRROStartDate" type="xs:string"/>

                      <xs:element name="PreviousIDRROEndDate" type="xs:string"/>

                      <xs:element name="CaseDetailsText" type="xs:string"/>

                      <xs:element name="CaseDetails">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="CaseNoCase"/>

                                  <xs:sequence minOccurs="0">

                                      <xs:element ref="CaseName"/>

                                      <xs:element ref="Court"/>

                                      <xs:element ref="CaseType"/>

                                      <xs:element ref="CourtNumber"/>

                                      <xs:element ref="CaseYear"/>

                                      <xs:element ref="StartDate"/>

                                  </xs:sequence>

                                  <xs:element ref="Status"/>

                                  <xs:element ref="AnnulDate" minOccurs="0"/>

                                  <xs:element ref="AnnulReason" minOccurs="0"/>

                                  <xs:element ref="CaseDescription"/>

                                  <xs:element ref="SpecialNote" minOccurs="0"/>

                                  <xs:element ref="TradingNames"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="CaseNoCase" type="xs:integer"/>

                      <xs:element name="CaseName" type="xs:string"/>

                      <xs:element name="Court" type="xs:string"/>

                      <xs:element name="CaseType" type="xs:string"/>

                      <xs:element name="CourtNumber" type="xs:string"/>

                      <xs:element name="CaseYear" type="xs:string"/>

                      <xs:element name="StartDate" type="xs:string"/>

                      <xs:element name="Status" type="xs:string"/>

                      <xs:element name="AnnulDate" type="xs:string"/>

                      <xs:element name="AnnulReason" type="xs:string"/>

                      <xs:element name="CaseDescription" type="xs:string"/>

                      <xs:element name="SpecialNote" type="xs:string"/>

                      <xs:element name="TradingNames">

                          <xs:complexType mixed="true">

                              <xs:choice minOccurs="0" maxOccurs="unbounded">

                                  <xs:element ref="TradingAddress"/>

                                  <xs:element ref="TradingName"/>

                              </xs:choice>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="TradingAddress" type="xs:string"/>

                      <xs:element name="TradingName" type="xs:string"/>

                      <xs:element name="InsolvencyPractitionerText" type="xs:string"/>

                      <xs:element name="IP">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="CaseNoIP"/>

                                  <xs:element ref="MainIP"/>

                                  <xs:element ref="MainIPFirm"/>

                                  <xs:element ref="MainIPFirmAddress"/>

                                  <xs:element ref="MainIPFirmPostCode"/>

                                  <xs:element ref="MainIPFirmTelephone"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="CaseNoIP" type="xs:integer"/>

                      <xs:element name="MainIP" type="xs:string"/>

                      <xs:element name="MainIPFirm" type="xs:string"/>

                      <xs:element name="MainIPFirmAddress" type="xs:string"/>

                      <xs:element name="MainIPFirmPostCode" type="xs:string"/>

                      <xs:element name="MainIPFirmTelephone" type="xs:string"/>

                      <xs:element name="InsolvencyContactText" type="xs:string"/>

                      <xs:element name="InsolvencyContact">

                          <xs:complexType>

                              <xs:sequence>

                                  <xs:element ref="CaseNoContact"/>

                                  <xs:element ref="InsolvencyServiceOffice"/>

                                  <xs:element ref="Contact"/>

                                  <xs:element ref="ContactAddress"/>

                                  <xs:element ref="ContactPostCode"/>

                                  <xs:element ref="ContactTelephone"/>

                              </xs:sequence>

                          </xs:complexType>

                      </xs:element>

                      <xs:element name="CaseNoContact" type="xs:integer"/>

                      <xs:element name="InsolvencyServiceOffice" type="xs:string"/>

                      <xs:element name="Contact" type="xs:string"/>

                      <xs:element name="ContactAddress" type="xs:string"/>

                      <xs:element name="ContactPostCode" type="xs:string"/>

                      <xs:element name="ContactTelephone" type="xs:string"/>

                  </xs:schema>

                   

                  To begin with at least, the queries being run against the data will all be about as simple as SQL gets, as the primary requirement is just to be able to retrieve data from multiple different elements of the XML file for a given "CaseNoIndividual", or to summarise data by a date range, e.g.

                   

                  SELECT  "FirstName",

                                 "Surname",

                                "Occupation",

                                "CaseName",

                                "Court",

                                "CaseType",

                                "TradingAddress",

                                "TradingName"

                  FROM .........

                  WHERE "CaseNoIndividual" = 123456

                   

                  Or

                   

                  SELECT DISTINCT "MainIPFirm",

                               COUNT("CaseNoIndividual") OVER (PARTITION BY "MainIPFirm")

                  FROM .........

                  WHERE "CaseType" = ......

                  AND TO_DATE("StartDate", 'DD/MM/YYYY') >= .........

                  AND TO_DATE("StartDate", 'DD/MM/YYYY') <= .........

                  ORDER BY "MainIPFirm" DESC

                   

                  Further down the line there might be more complex demands though. Ultimately I just want to get the data in the XML file into either one big table or multiple smaller tables so that I can query it as I would any data held in a bog-standard Oracle table.

                  • 6. Re: Re: Loading XML data into an XML schema
                    Marco Gralike

                    Try it yourself, otherwise you won't learn anything.

                     

                    See XMLDB Developers Guide regarding the how/what/where

                     

                    An example...

                     

                    -- -----------------------------------------------------------------------------------------------------
                    CREATE OR REPLACE DIRECTORY XML_CONTENT_DIR as 'c:\temp';
                    -- -----------------------------------------------------------------------------------------------------
                    CALL dbms_xmlschema.deleteschema('myXMLSchema.xsd',4);
                    -- -----------------------------------------------------------------------------------------------------
                    BEGIN
                      --
                      DBMS_XMLSCHEMA.registerSchema (
                      SCHEMAURL => 'myXMLSchema.xsd',
                      SCHEMADOC => xmltype(bfilename('XML_CONTENT_DIR','myXMLSchema.xsd'),nls_charset_id('AL32UTF8')),
                      LOCAL     => TRUE,        -- local
                      GENTYPES  => FALSE,       -- generate object types
                      GENBEAN   => FALSE,       -- no java beans
                      GENTABLES => FALSE,       -- generate object tables
                      FORCE     => FALSE,       -- cyclic dependancies in xsd
                      OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML, -- register for binary XML only
                      OWNER     => USER );
                      --
                      COMMIT;
                      --
                    END;
                    /
                    -- -----------------------------------------------------------------------------------------------------
                    DROP TABLE TABLE_OBJECT PURGE;
                    -- -----------------------------------------------------------------------------------------------------
                    CREATE TABLE TABLE_OBJECT
                      ( COLUMN_OBJECT_ID   NUMBER,
                        STATUS                 VARCHAR2(64 BYTE),
                        TCH_TIJDSTIP_ONTSTAAN  TIMESTAMP,
                        TCH_TIJDSTIP_VERVALLEN TIMESTAMP,
                        LGS_TIJDSTIP_ONTSTAAN  TIMESTAMP,
                        LGS_TIJDSTIP_VERVALLEN TIMESTAMP,
                        IDENTIFICATIE          VARCHAR2(64 BYTE),
                        COLUMN_OBJECT      XMLTYPE NOT NULL,
                        TDM_VERSIE             VARCHAR2(16 BYTE)
                      )
                     NOCOMPRESS NOLOGGING
                      TABLESPACE "USERS" 
                      XMLTYPE COLUMN "COLUMN_OBJECT" STORE AS SECUREFILE BINARY XML  
                      ( TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 CACHE  NOCOMPRESS  KEEP_DUPLICATES ) 
                      XMLSCHEMA "myXMLSchema.xsd" ELEMENT "myXMLSchema" ID 4049 DISALLOW NONSCHEMA 
                      ;
                    -- -----------------------------------------------------------------------------------------------------
                    CREATE INDEX K_XML_IX
                          ON TABLE_OBJECT(COLUMN_OBJECT)
                          INDEXTYPE IS XDB.XMLINDEX
                    --      LOCAL
                          PARAMETERS
                              ( q'# PATHS (EXCLUDE (/)
                                           NAMESPACE MAPPING (xmlns="http://www.company.nl/koers/tdm/myXMLSchema")
                                          )
                                          PATH TABLE       K_XML_PATH_TABLE
                                          PIKEY      INDEX K_XML_PIKEY     
                                          PATH ID    INDEX K_XML_PATH_ID   
                                          ORDER KEY  INDEX K_XML_ORDER_KEY 
                                          VALUE      INDEX K_XML_VALUE     
                                          ASYNC (SYNC ALWAYS) STALE (FALSE)
                                #' );
                    -- -----------------------------------------------------------------------------------------------------
                    ALTER INDEX K_XML_IX
                        PARAMETERS ( q'# ADD_GROUP GROUP SXI_GROUP_01
                                         XMLTABLE K_XML_SXI_01
                                         XMLNAMESPACES(DEFAULT 'http://www.company.nl/koers/tdm/myXMLSchema'),
                                         '/myXMLSchema/stukdeelReferentie'
                                         COLUMNS
                                           stukTechnischId NUMBER(10)    PATH 'stukTechnischId'
                                         , soortReferentie VARCHAR2(128) PATH 'soortReferentie'
                                       #' );
                    -- -----------------------------------------------------------------------------------------------------                                                             
                    ALTER INDEX K_XML_IX
                        PARAMETERS ( q'# ADD_GROUP GROUP SXI_GROUP_02
                                         XMLTABLE K_XML_SXI_02
                                         XMLNAMESPACES(DEFAULT 'http://www.company.nl/koers/tdm/myXMLSchema'),
                                         '/myXMLSchema/onroerendeZaak/kadastraleAanduiding'
                                         COLUMNS
                                         aanduiding VARCHAR2(128) PATH 'aanduiding'
                                       #' );
                    -- -----------------------------------------------------------------------------------------------------
                    -- Extra index op: rid als laatste of als eerste kolom van index.
                    -- -----------------------------------------------------------------------------------------------------
                    CREATE UNIQUE INDEX IX_SECONDARY_01 ON K_XML_SXI_01(stuktechnischid, soortreferentie, rid);
                    --
                    INSERT INTO TABLE_OBJECT (COLUMN_OBJECT)
                    SELECT XMLTYPE(bfilename('XML_CONTENT_DIR','myXMLSchema.xml'),nls_charset_id('AL32UTF8'))
                    FROM DUAL;
                    --
                    COMMIT;
                    -- -----------------------------------------------------------------------------------------------------
                    -- Testing WELLFORMED, LAX, STRICT valdition ERRORS
                    -- -----------------------------------------------------------------------------------------------------
                    -- *) LAX / STRICT validation info: https://community.oracle.com/thread/418458 (FAQ XMLDB)
                    -- -----------------------------------------------------------------------------------------------------
                    INSERT --> XML is not XML Wellformed (see Wikipedia)
                    INTO TABLE_OBJECT (COLUMN_OBJECT)
                    SELECT XMLTYPE(bfilename('XML_CONTENT_DIR','myXMLSchema-NOTXML-error.xml'),nls_charset_id('AL32UTF8')) 
                    FROM DUAL;
                    -- 
                    INSERT 
                    -- XML errors out on XSD LAX/LAZY validation 
                    -- Incorrect structure
                    INTO TABLE_OBJECT (COLUMN_OBJECT)
                    SELECT XMLTYPE(bfilename('XML_CONTENT_DIR','myXMLSchema-LAX-error.xml'),nls_charset_id('AL32UTF8')) 
                    FROM DUAL;
                    -- 
                    INSERT 
                    -- XML errors out on XSD STRICT validation (incorrect expected content) 
                    -- String instead of expected datetime
                    INTO TABLE_OBJECT (COLUMN_OBJECT)
                    SELECT XMLTYPE(bfilename('XML_CONTENT_DIR','myXMLSchema-STRICT1-error.xml'),nls_charset_id('AL32UTF8')) 
                    FROM DUAL;
                    -- 
                    INSERT 
                    -- XML errors out on XSD STRICT validation (incorrect expected content) 
                    -- datetime in incorrect XML datetime format
                    INTO TABLE_OBJECT (COLUMN_OBJECT)
                    SELECT XMLTYPE(bfilename('XML_CONTENT_DIR','myXMLSchema-STRICT2-error.xml'),nls_charset_id('AL32UTF8')) 
                    FROM DUAL;
                    -- -----------------------------------------------------------------------------------------------------
                    -- SELECTIES
                    -- -----------------------------------------------------------------------------------------------------
                    explain plan for
                    select *
                    from TABLE_OBJECT
                    where IDENTIFICATIE = 'NL.IMKAD.myXMLSchema.168103014700000'
                    /
                    select * from table(dbms_xplan.display)
                    /
                    -- -----------------------------------------------------------------------------------------------------
                    -- EOF
                    -- -----------------------------------------------------------------------------------------------------
                    
                    • 7. Re: Loading XML data into an XML schema
                      odie_63

                      Ultimately I just want to get the data in the XML file into either one big table or multiple smaller tables so that I can query it as I would any data held in a bog-standard Oracle table.

                      My bad, that's usually the first question I ask when someone wants to use OR storage :

                      Do you want to persist XML data in the database and query it more than once?

                       

                      By the quote above, I guess you don't.

                      Therefore you don't need to use OR storage, or even register the schema (unless you want validation of the structure).

                      Assuming you're on 11g and onwards, use a staging Binary XML storage and query XML using XMLTABLE to directly populate your target relational tables.

                      • 8. Re: Loading XML data into an XML schema
                        3019429

                        Believe me Marco, I've been trying it myself for almost a week and have succeeded only in learning that the multiple different approaches that I've tried don't seem to work. If I had no deadlines I'd continue trying to work this out myself but unfortunately I do :-)

                         

                        Odie- yes I will need to query the data repeatedly as it will be used to generate reports. I suppose the best way I can explain what I need to do is to say that if I had, for example, the data file in .csv format, I'd just simply create an external table and then use a simple CREATE TABLE AS SELECT * FROM... to create a standard Oracle. I'm trying to do exactly the same with this XML file.

                         

                        So in this instance I thought that creating the XML schema and loading the data into it would be the equivalent of the external table in my example above, and I'd then easily be able to query that as and when I needed to, create other Oracle DB objects using it etc. Thanks to your blog post that you linked to earlier, I think I've managed to load the data into the XML schema that I've registered, but querying that data is what has got me stumped.

                         

                        Originally I was working with a test data file (sample_data.xml in the query below) which was very easy to load into an Oracle table using this simple insert statement:

                         

                        INSERT INTO xml_data_table (

                                                                 extract_date,

                                                                 case_no_report_request,

                                                                 case_no_individual,

                                                                 title,

                                                                 gender,

                                                                 first_name,

                                                                 surname,

                                                                 occupation,

                                                                 date_of_birth,

                                                                 deceased_date,

                                                                 last_known_address,

                                                                 last_known_postcode,

                                                                 other_names,

                                                                 other_name,

                                                                 restrictions_type,

                                                                 restrictions_start_date,

                                                                 restrictions_end_date,

                                                                 restrictions_court,

                                                                 restrictions_court_no,

                                                                 restrictions_case_year,

                                                                 previous_ibro,

                                                                 previous_ibro_note,

                                                                 previous_ibro_start_date,

                                                                 previous_ibro_end_date,

                                                                 dro_restrictions_type,

                                                                 dro_restrictions_start_date,

                                                                 dro_restrictions_end_date,

                                                                 previous_idrro,

                                                                 previous_idrro_note,

                                                                 previous_idrro_start_date,

                                                                 previous_idrro_end_date,

                                                                 case_no_case,

                                                                 case_name,

                                                                 court,

                                                                 case_type,

                                                                 court_number,

                                                                 case_year,

                                                                 start_date,

                                                                 status,

                                                                 annul_date,

                                                                 annul_reason,

                                                                 case_description,

                                                                 special_note,

                                                                 trading_names,

                                                                 trading_address,

                                                                 trading_name,

                                                                 case_no_ip,

                                                                 main_ip,

                                                                 main_ip_firm,

                                                                 main_ip_firm_address,

                                                                 main_ip_firm_postcode,

                                                                 main_ip_firm_telephone,

                                                                 case_no_contact,

                                                                 insolvency_service_office,

                                                                 contact,

                                                                 contact_address,

                                                                 contact_postcode,

                                                                 contact_telephone                    

                                                                )

                                     SELECT x.*

                                       FROM XMLTABLE (

                                                      '/ReportDetails/ReportRequest'

                                                      PASSING XMLTYPE(BFILENAME('LANDING_AREA','sample_data.xml'), NLS_CHARSET_ID('AL32UTF8'))

                                                      COLUMNS extract_date                VARCHAR2(10)  PATH 'ExtractDate',

                                                              case_no_report_request      NUMBER        PATH 'CaseNoReportRequest',

                                                              case_no_individual          NUMBER        PATH 'IndividualDetails/CaseNoIndividual',

                                                              title                       VARCHAR2(20)  PATH 'IndividualDetails/Title',

                                                              gender                      VARCHAR2(20)  PATH 'IndividualDetails/Gender',

                                                              first_name                  VARCHAR2(30)  PATH 'IndividualDetails/FirstName',

                                                              surname                     VARCHAR2(30)  PATH 'IndividualDetails/Surname',

                                                              occupation                  VARCHAR2(50)  PATH 'IndividualDetails/Occupation',

                                                              date_of_birth               VARCHAR2(25)  PATH 'IndividualDetails/DateofBirth',

                                                              deceased_date               VARCHAR2(25)  PATH 'IndividualDetails/DeceasedDate',

                                                              last_known_address          VARCHAR2(80)  PATH 'IndividualDetails/LastKnownAddress',

                                                              last_known_postcode         VARCHAR2(50)  PATH 'IndividualDetails/LastKnownPostCode',

                                                              other_names                 VARCHAR2(40)  PATH 'IndividualDetails/OtherNames',

                                                              other_name                  VARCHAR2(40)  PATH 'IndividualDetails/OtherNames/OtherName',

                                                              restrictions_type           VARCHAR2(10)  PATH 'BankruptcyRestrictionsDetails/RestrictionsType',

                                                              restrictions_start_date     VARCHAR2(10)  PATH 'BankruptcyRestrictionsDetails/RestrictionsStartDate',

                                                              restrictions_end_date       VARCHAR2(10)  PATH 'BankruptcyRestrictionsDetails/RestrictionsEndDate',

                                                              restrictions_court          VARCHAR2(40)  PATH 'BankruptcyRestrictionsDetails/RestrictionsCourt',

                                                              restrictions_court_no       NUMBER        PATH 'BankruptcyRestrictionsDetails/RestrictionsCourtNo',

                                                              restrictions_case_year      NUMBER        PATH 'BankruptcyRestrictionsDetails/RestrictionsCaseYear',

                                                              previous_ibro               VARCHAR2(10)  PATH 'BankruptcyRestrictionsDetails/PreviousIBRO',

                                                              previous_ibro_note          VARCHAR2(100) PATH 'BankruptcyRestrictionsDetails/PreviousIBRO/PreviousIBRONote',

                                                              previous_ibro_start_date    DATE          PATH 'BankruptcyRestrictionsDetails/PreviousIBRO/PreviousIBROStartDate',

                                                              previous_ibro_end_date      DATE          PATH 'BankruptcyRestrictionsDetails/PreviousIBRO/PreviousIBROEndDate',

                                                              dro_restrictions_type       VARCHAR2(50)  PATH 'DebtReliefRestrictionsDetails/DRORestrictionsType',

                                                              dro_restrictions_start_date VARCHAR2(10)  PATH 'DebtReliefRestrictionsDetails/DRORestrictionsStartDate',

                                                              dro_restrictions_end_date   VARCHAR2(10)  PATH 'DebtReliefRestrictionsDetails/DRORestrictionsEndDate',

                                                              previous_idrro              VARCHAR2(20)  PATH 'DebtReliefRestrictionsDetails/PreviousIDRRO',

                                                              previous_idrro_note         VARCHAR2(20)  PATH 'DebtReliefRestrictionsDetails/PreviousIDRRO/PreviousIDRRONote',

                                                              previous_idrro_start_date   DATE          PATH 'DebtReliefRestrictionsDetails/PreviousIDRRO/PreviousIDRROStartDate',

                                                              previous_idrro_end_date     DATE          PATH 'DebtReliefRestrictionsDetails/PreviousIDRRO/PreviousIDRROEndDate',

                                                              case_no_case                NUMBER        PATH 'CaseDetails/CaseNoCase',

                                                              case_name                   VARCHAR2(50)  PATH 'CaseDetails/CaseName',

                                                              court                       VARCHAR2(40)  PATH 'CaseDetails/Court',

                                                              case_type                   VARCHAR2(40)  PATH 'CaseDetails/CaseType',

                                                              court_number                VARCHAR2(10)  PATH 'CaseDetails/CourtNumber',

                                                              case_year                   VARCHAR2(40)  PATH 'CaseDetails/CaseYear',

                                                              start_date                  VARCHAR2(10)  PATH 'CaseDetails/StartDate',

                                                              status                      VARCHAR2(90)  PATH 'CaseDetails/Status',

                                                              annul_date                  VARCHAR2(10)  PATH 'CaseDetails/AnnulDate',

                                                              annul_reason                VARCHAR2(40)  PATH 'CaseDetails/AnnulReason',

                                                              case_description            VARCHAR2(220) PATH 'CaseDetails/CaseDescription',

                                                              special_note                VARCHAR2(50)  PATH 'CaseDetails/SpecialNote',

                                                              trading_names               VARCHAR2(100) PATH 'CaseDetails/TradingNames',

                                                              trading_address             VARCHAR2(50)  PATH 'CaseDetails/TradingNames/TradingAddress',

                                                              trading_name                VARCHAR2(50)  PATH 'CaseDetails/TradingNames/TradingName',

                                                              case_no_ip                  NUMBER        PATH 'IP/CaseNoIP',

                                                              main_ip                     VARCHAR2(50)  PATH 'IP/MainIP',

                                                              main_ip_firm                VARCHAR2(50)  PATH 'IP/MainIPFirm',

                                                              main_ip_firm_address        VARCHAR2(50)  PATH 'IP/MainIPFirmAddress',

                                                              main_ip_firm_postcode       VARCHAR2(10)  PATH 'IP/MainIPFirmPostcode',

                                                              main_ip_firm_telephone      VARCHAR2(20)  PATH 'IP/MainIPFirmTelephone',

                                                              case_no_contact             NUMBER        PATH 'InsolvencyContact/CaseNoContact',

                                                              insolvency_service_office   VARCHAR2(20)  PATH 'InsolvencyContact/InsolvencyServiceOffice',

                                                              contact                     VARCHAR2(30)  PATH 'InsolvencyContact/Contact',

                                                              contact_address             VARCHAR2(80)  PATH 'InsolvencyContact/ContactAddress',

                                                              contact_postcode            VARCHAR2(10)  PATH 'InsolvencyContact/ContactPostCode',

                                                              contact_telephone           VARCHAR2(20)  PATH 'InsolvencyContact/ContactTelephone'

                                                     ) x;

                         

                        COMMIT;

                         

                        Unfortunately this doesn't work with the new XML data I've been given though, because in the new data some of the lowest-level elements can appear multiple times (e.g. "TradingName") which then throws up the error 'ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence'.

                        • 9. Re: Loading XML data into an XML schema
                          odie_63

                          Unfortunately this doesn't work with the new XML data I've been given though, because in the new data some of the lowest-level elements can appear multiple times (e.g. "TradingName") which then throws up the error 'ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence'.

                          That's why you obviously need to create additional (child) tables, one for each group that may - as per the XSD - occur multiple times, not just a single big one.

                          You can then load them the same way, using XMLTABLE.

                          As the XSD doesn't seem to define any natural IDs, those tables will have to be linked together by PK/FK relationships, using surrogate keys generated from sequences.

                           

                          If you want to keep the data in an XMLType table (stored Object-Relationally) instead of populating regular tables, then you can alternatively create relational views over it, using XMLTABLE.

                          The issue of maintaining the relationship between parent and child entities still holds though.