7 Replies Latest reply: Aug 5, 2012 12:49 AM by user628754 RSS

    Loading XML Into a table

    user628754
      I have been assigned to a project that requires some data to be extracted from XML files and loaded into data tables. I need to get up to speed on the technology before I am asked to proceed at speed in October.
      I have been experimenting on the 11G XE database. This appears to have all the necessary applications.

      I am attempting to load some data using the following procedure.

      create or replace
      Procedure TestXmlLoad AS
      cursor operator_files_cur is
      SELECT path
      from path_view
      where under_path(res, '/public/demo/xml',1) > 0;
      BEGIN
      for operator_rec in operator_files_cur loop
      BEGIN
      insert into operator_xml values(xdburitype(operator_rec.path).getxml());
      END;
      end loop;
      END;

      This is throwing the following error.

      Connecting to the database BusSpeeds.
      ORA-31000: Resource 'file:///C:/oraclexe/xmldb/xsd/Operator.xsd' is not an XDB schema document
      ORA-06512: at "BUS_SPEEDS.TESTXMLLOAD", line 10
      ORA-06512: at line 2

      The table was created with the following statement:

      CREATE TABLE operator_xml OF XMLTYPE XMLTYPE STORE AS SECUREFILE
      BINARY XML XMLSCHEMA "http://localhost:8080/public/demo/xsdii/operator.xsd" ELEMENT "Network_Data";

      I registered the Operator.xsd file with the following statement.

      BEGIN
      DBMS_XMLSCHEMA.registerSchema('http://localhost:8080/public/demo/xsdii/operator.xsd',
      XMLType(bfilename('XSD_DIR','/operator.xsd'),nls_charset_id('AL32UTF8')),
      genTypes => FALSE,
      genTables => FALSE,
      options => DBMS_XMLSCHEMA.REGISTER_BINARYXML);
      END;

      There are a number of schemas registered with the database. As I understand it, i have to purge these from the database to remove them. The trouble is that I am getting access rights and privilege errors when I attempt to do this,

      select schema_url from user_xml_schemas

      http://localhost:8080/public/demo/xsd/Operator_20120224.xsd
      http://localhost:8080/public/demo/xsd/Operator.xsd
      http://localhost:8080/public/demo/xsd/Operator_xdb.xsd
      http://localhost:8080/public/demo/xsd/operator.xsd
      http://localhost:8080/public/demo/xsd/operator_glob.xsd
      http://localhost:8080/public/demo/xsdii/operator.xsd
      http://localhost:8080/public/demo/xsd/db_objects.xsd

      I have included a copy of the Operator *.xsd file.

      <?xml version="1.0" encoding="UTF-8"?>
      <!-- edited with XMLSpy v2009 sp1 (http://www.altova.com) by Urs Widler (Continental Automotive Switzerland AG) -->
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:op="http://localhost:8080/public/demo/xsd/Operator" xmlns:bv="http://localhost:8080/public/demo/xsd/Base_Version" elementFormDefault="unqualified" attributeFormDefault="unqualified">
           <xs:element name="Network_Data">
                <xs:annotation>
                     <xs:documentation>Static data for CDII interface</xs:documentation>
                </xs:annotation>
                <xs:complexType>
                     <xs:sequence>
                          <xs:element name="Base_Version">
                               <xs:simpleType>
                                    <xs:restriction base="xs:integer">
                                         <xs:totalDigits value="8"/>
                                    </xs:restriction>
                               </xs:simpleType>
                          </xs:element>
                          <xs:element name="Operator" minOccurs="0" maxOccurs="unbounded">
                               <xs:complexType>
                                    <xs:sequence>
                                         <xs:element name="Operator_Name" nillable="true">
                                              <xs:simpleType>
                                                   <xs:restriction base="xs:string">
                                                        <xs:minLength value="0"/>
                                                        <xs:maxLength value="128"/>
                                                   </xs:restriction>
                                              </xs:simpleType>
                                         </xs:element>
                                         <xs:element name="Operator_Agency" nillable="true">
                                              <xs:simpleType>
                                                   <xs:restriction base="xs:string">
                                                        <xs:minLength value="0"/>
                                                        <xs:maxLength value="128"/>
                                                   </xs:restriction>
                                              </xs:simpleType>
                                         </xs:element>
                                    </xs:sequence>
                                    <xs:attribute name="aOperator_Code" use="required">
                                         <xs:simpleType>
                                              <xs:restriction base="xs:string">
                                                   <xs:minLength value="0"/>
                                                   <xs:maxLength value="10"/>
                                              </xs:restriction>
                                         </xs:simpleType>
                                    </xs:attribute>
                               </xs:complexType>
                          </xs:element>
                     </xs:sequence>
                </xs:complexType>
                <xs:key name="Operator_Code_PKey">
                     <xs:selector xpath="Operator"/>
                     <xs:field xpath="@aOperator_Code"/>
                </xs:key>
           </xs:element>
      </xs:schema>

      Unfortunately the *.xml file is a bit big.

      Can anyone help? How do I start to unravel this problem?
        • 1. Re: Loading XML Into a table
          odie_63
          Hi,
          ORA-31000: Resource 'file:///C:/oraclexe/xmldb/xsd/Operator.xsd' is not an XDB schema document
          That means your XML file contains an <tt>xsi:noNamespaceSchemaLocation</tt> (or <tt>xsi:schemaLocation</tt>) attribute pointing to "C:/oraclexe/xmldb/xsd/Operator.xsd", a local resource.
          That reference has to be replaced by the actual URL used to register the schema in the DB.

          For clarity, you may also find useful to register schemas with short URL names. It's not mandatory to use the http:// scheme.
          But it's just a personal preference, the important point is to be consistent throughout the process.

          There are a number of schemas registered with the database. As I understand it, i have to purge these from the database to remove them. The trouble is that I am getting access rights and privilege errors when I attempt to do this,
          If you're correcting the XML file instances, normally you don't have to do anything to the schemas.

          What error are you getting exactly?

          You should be able to delete the schemas via DBMS_XMLSchema.deleteSchema procedure, using the same user who registered them.
          If there are XMLType tables referencing the schemas, drop them first.
          If there are dependencies between the schemas, delete them in order, and for circular dependencies use the DELETE_CASCADE_FORCE option.


          BTW, you may want to post in the dedicated forum next time : {forum:id=34}

          Edited by: odie_63 on 2 août 2012 11:07
          • 2. Re: Loading XML Into a table
            user628754
            Thanks,

            Perhaps the XML file is not that big, See listing!

            ?xml version="1.0" encoding="UTF-8"?>
            <op:Network_Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            op:noNamespaceSchemaLocation="file:///C:/oraclexe/xmldb/xsd/Operator.xsd"
            xmlns="http://www.tfl.uk/CDII/Operator_20120224">
                 <Base_Version>20120224</Base_Version>
                 <Operator aOperator_Code="BE">
                      <Operator_Name>BLUE TRIANGLE</Operator_Name>
                      <Operator_Agency>Go-Ahead</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="CC">
                      <Operator_Name>FIRST LONDON EAST</Operator_Name>
                      <Operator_Agency>First</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="CV">
                      <Operator_Name>THAMES CLIPPERS</Operator_Name>
                      <Operator_Agency>Thames Clippers</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="CW">
                      <Operator_Name>FIRST LONDON WEST</Operator_Name>
                      <Operator_Agency>First</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="CX">
                      <Operator_Name>ABELLIO LONDON</Operator_Name>
                      <Operator_Agency>Abellio London</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="DC">
                      <Operator_Name>DOCKLANDS BUSES</Operator_Name>
                      <Operator_Agency>Go-Ahead</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="EB">
                      <Operator_Name>QUALITY LINE</Operator_Name>
                      <Operator_Agency>Quality Line</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="ET">
                      <Operator_Name>EAST THAMES BUSES</Operator_Name>
                      <Operator_Agency>Go-Ahead</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="EY">
                      <Operator_Name>E C T BUS CIC</Operator_Name>
                      <Operator_Agency>E C T Bus</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="HY">
                      <Operator_Name>C T PLUS</Operator_Name>
                      <Operator_Agency>C T Plus</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="IF">
                      <Operator_Name>EAST LONDON</Operator_Name>
                      <Operator_Agency>East London Bus Group</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="KE">
                      <Operator_Name>ARRIVA KENT THAMESIDE</Operator_Name>
                      <Operator_Agency>Arriva Kent Thameside</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="LC">
                      <Operator_Name>LONDON CENTRAL</Operator_Name>
                      <Operator_Agency>Go-Ahead</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="LD">
                      <Operator_Name>ARRIVA THE SHIRES</Operator_Name>
                      <Operator_Agency>Arriva The Shires</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="LG">
                      <Operator_Name>LONDON GENERAL</Operator_Name>
                      <Operator_Agency>Go-Ahead</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="LU">
                      <Operator_Name>LONDON UNITED</Operator_Name>
                      <Operator_Agency>London United</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="ME">
                      <Operator_Name>METROBUS</Operator_Name>
                      <Operator_Agency>Metrobus</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="ML">
                      <Operator_Name>METROLINE</Operator_Name>
                      <Operator_Agency>Metroline</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="MN">
                      <Operator_Name>ARRIVA LONDON NORTH</Operator_Name>
                      <Operator_Agency>Arriva</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="NCP">
                      <Operator_Name>N C P CHALLENGER</Operator_Name>
                      <Operator_Agency>NSL Limited</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="NL">
                      <Operator_Name>N S L</Operator_Name>
                      <Operator_Agency>London United</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="OL">
                      <Operator_Name>ARRIVA WANDSWORTH</Operator_Name>
                      <Operator_Agency>Arriva Wandsworth</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="SK">
                      <Operator_Name>SELKENT</Operator_Name>
                      <Operator_Agency>East London Bus Group</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="SL">
                      <Operator_Name>ARRIVA LONDON SOUTH</Operator_Name>
                      <Operator_Agency>Arriva</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="SN">
                      <Operator_Name>SULLIVAN BUSES</Operator_Name>
                      <Operator_Agency>Sullivan Buses</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="SV">
                      <Operator_Name>LONDON SOVEREIGN</Operator_Name>
                      <Operator_Agency>London Sovereign</Operator_Agency>
                 </Operator>
                 <Operator aOperator_Code="TE">
                      <Operator_Name>ABELLIO LONDON (WEST)</Operator_Name>
                      <Operator_Agency>Abellio London</Operator_Agency>
                 </Operator>
            </op:Network_Data>

            I believe the crucial line is:
            op:noNamespaceSchemaLocation="file:///C:/oraclexe/xmldb/xsd/Operator.xsd"

            Otherwise it is still raising the same error!

            DBMS_XMLSchema.deleteSchema appears to remove the schema registrations.

            The reason i asked was because there were a number of Operator.xsd files referenced!
            I was not sure which one was being referenced!

            Any other ideas appreciated!
            • 3. Re: Loading XML Into a table
              odie_63
              I believe the crucial line is:
              op:noNamespaceSchemaLocation="file:///C:/oraclexe/xmldb/xsd/Operator.xsd"
              Yes.
              As I said above, you have to replace that with the URL of the registered schema.

              And your XML file is not valid anyway :
              ?xml version="1.0" encoding="UTF-8"?>
              <op:Network_Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              op:noNamespaceSchemaLocation="file:///C:/oraclexe/xmldb/xsd/Operator.xsd"
              xmlns="http://www.tfl.uk/CDII/Operator_20120224">
              The "op" prefix is not tied to any namespace, and the noNamespaceSchemaLocation attribute should be qualified with "xsi"...

              Something like this would make more sense :
              <?xml version="1.0" encoding="UTF-8"?>
              <op:Network_Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                 xsi:noNamespaceSchemaLocation="URL-used-to-register-the-schema" 
                 xmlns:op="http://www.tfl.uk/CDII/Operator_20120224">
              ...
              • 4. Re: Loading XML Into a table
                user628754
                So I re-factored the *.xml file to compile properly in XMLSPY and arrived at this. But i still get the same error.

                <?xml version="1.0" encoding="UTF-8"?>
                <Network_Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="file:///C:/oraclexe/xmldb/xsd/Operator.xsd">
                     <Base_Version>20120224</Base_Version>
                     <Operator aOperator_Code="BE">
                          <Operator_Name>BLUE TRIANGLE</Operator_Name>
                          <Operator_Agency>Go-Ahead</Operator_Agency>
                     </Operator><Operator aOperator_Code="CC">
                          <Operator_Name>FIRST LONDON EAST</Operator_Name>
                          <Operator_Agency>First</Operator_Agency>
                     </Operator>
                .
                .
                .
                     </Network_Data>

                Any other ideas would be greatly appreciated!

                Edited by: user628754 on Aug 2, 2012 9:15 PM
                • 5. Re: Loading XML Into a table
                  user628754
                  So I refactored the *.xml file to look like this and compile properly in *.xmlSpy

                  <?xml version="1.0" encoding="UTF-8"?>
                  <Network_Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="file:///C:/oraclexe/xmldb/xsd/Operator.xsd">
                       <Base_Version>20120224</Base_Version>
                       <Operator aOperator_Code="BE">
                            <Operator_Name>BLUE TRIANGLE</Operator_Name>
                            <Operator_Agency>Go-Ahead</Operator_Agency>
                       </Operator><Operator aOperator_Code="CC">
                            <Operator_Name>FIRST LONDON EAST</Operator_Name>
                            <Operator_Agency>First</Operator_Agency>
                       </Operator>
                       </Network_Data>

                  But i still get the same error.
                  Any further ideas would be greatly appreciated.
                  • 6. Re: Loading XML Into a table
                    odie_63
                    Did you read any of my replies?

                    For security reasons, XML DB doesn't allow access to resource on the local filesystem.
                    So I'll repeat once again :

                    You have to replace this :
                    xsi:noNamespaceSchemaLocation="file:///C:/oraclexe/xmldb/xsd/Operator.xsd"
                    with this :
                    xsi:noNamespaceSchemaLocation="URL-used-to-register-the-schema"
                    In your case, assuming the schema has been registered with URL "http://localhost:8080/public/demo/xsdii/operator.xsd", then the file should be :
                    <?xml version="1.0" encoding="UTF-8"?>
                    <Network_Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/public/demo/xsdii/operator.xsd">
                    <Base_Version>20120224</Base_Version>
                    ...
                    • 7. Re: Loading XML Into a table
                      user628754
                      Thanks for time and patience,

                      I would not have been able to move this project forward without your help!