1 2 Previous Next 15 Replies Latest reply on Jul 12, 2016 2:23 PM by odie_63

    Import XML data

    lrs

      How can I import data from an XML document to an Oracle database using SQL?

      Thanks!

        • 1. Re: Import XML data
          Andrew Sayer

          8343b271-8bf7-4b86-9cd5-dd048b49fa5c wrote:

           

          How can I import data from an XML document to an Oracle database using SQL?

          Thanks!

          You can use SQL LDR

          https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb25loa.htm

           

          If you're wondering how to store it, there's an in built XMLTYPE data type specifically designed for this.

          • 2. Re: Import XML data
            John Thorton

            8343b271-8bf7-4b86-9cd5-dd048b49fa5c wrote:

             

            How can I import data from an XML document to an Oracle database using SQL?

            Thanks!

             

            unwilling or incapable to use GOOGLE yourself?

             

            https://www.google.com/search?q=How+can+I+import+data+from+an+XML+document+to+an+Oracle+database+using+SQL

            • 3. Re: Import XML data
              odie_63

              8343b271-8bf7-4b86-9cd5-dd048b49fa5c wrote:

               

              How can I import data from an XML document to an Oracle database using SQL?

              Thanks!

              By "import data" I assume you mean extract data from the document and store it in relational table(s)?

               

              What's your database version?

              Where's the XML document located? File, another column (data type?), PL/SQL variable?

              What data do you want to extract? Please post a sample doc and expected output.

              • 4. Re: Import XML data
                BluShadow

                Aside from our resident XML expert Odie.... you may wan to pop on over to the XML DB space which is dedicated to dealing with XML in the database...

                 

                XML DB

                 

                The XML DB FAQ also gives links to various discussions about methods for getting XML in to the database, depending on your various needs or requirements.

                • 5. Re: Import XML data
                  lrs

                  When I run the first bock of code of the:

                  “Example 29-1 Loading Very Large XML Documents Into Oracle Database Using SQL”:

                  https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb25loa.htm

                   

                  The result is:

                  Warning: la ejecución ha terminado con advertencias

                  TYPE person_t Compilado.

                  And no table is created. Why?

                   

                  This is the code:

                   

                  CREATE TYPE person_t AS OBJECT(name VARCHAR2(100), city VARCHAR2(100));/

                  BEGIN

                    -- Delete schema if it already exists (else error)

                  DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/person.xsd', 4);

                  END;/

                  BEGIN

                  DBMS_XMLSCHEMA.registerschema('http://www.oracle.com/person.xsd',

                  '<schema xmlns="http://www.w3.org/2001/XMLSchema"' ||

                  ' xmlns:per="http://www.oracle.com/person.xsd"' ||

                  ' xmlns:xdb="http://xmlns.oracle.com/xdb"' ||

                  ' elementFormDefault="qualified"' ||

                  ' targetNamespace="http://www.oracle.com/person.xsd">' ||

                       ' <element name="person" type="per:persontype"' ||

                  ' xdb:SQLType="PERSON_T"/>' ||

                       ' <complexType name="persontype" xdb:SQLType="PERSON_T">' ||

                         ' <sequence>' ||

                           ' <element name="name" type="string" xdb:SQLName="NAME"' ||

                  ' xdb:SQLType="VARCHAR2"/>' ||

                           ' <element name="city" type="string" xdb:SQLName="CITY"' ||

                  ' xdb:SQLType="VARCHAR2"/>' ||

                         ' </sequence>' ||

                       ' </complexType>' ||

                     ' </schema>',

                      TRUE,

                      FALSE,

                  FALSE);

                  END;/

                  CREATE TABLE foo OF XMLType

                  XMLSCHEMA "http://www.oracle.com/person.xsd" ELEMENT "person";

                  • 6. Re: Import XML data
                    odie_63

                    That's an example of Object-Relational storage.

                    I wouldn't be looking into that right now, especially if you're new to XML processing in Oracle

                     

                    Please clearly state what you're trying to do and provide the information requested earlier.

                    We'll then be able to suggest and give working examples of what you can do.

                    • 7. Re: Import XML data
                      lrs

                      Of course I am new working on XML.

                       

                      I just want to put on a local drive the XML file and the Schema file, and import the data using SQL to an Oracle table.

                      • 8. Re: Import XML data
                        odie_63

                        Still no answer to my questions...

                         

                        You won't need to use the XML schema unless the input file is really (really) big or if you want to persist the data as XML in the database (doesn't seem so).

                        Here's a basic example from the XML DB forum : Loading XML in relational table

                        • 9. Re: Import XML data
                          lrs

                          OK Odie, I will try the example you mention.


                          However, I thought it was also possible to create the table in Oracle using SQL according to Schema specifications.

                           

                          Is what the example I pasted above does?

                          • 10. Re: Import XML data
                            odie_63

                            3272116 wrote:

                             

                            However, I thought it was also possible to create the table in Oracle using SQL according to Schema specifications.

                             

                            Is what the example I pasted above does?

                            Close, but not exactly.

                             

                            When you register an XML schema into the database (with param genTypes = true), Oracle creates a set of SQL object types and collections to map the XML data model defined in the schema to the SQL data model.

                            That is called Object-Relational storage.

                            If a schema-based XMLType table or column is created using this model, Oracle creates additional hidden columns and nested tables to physically store XML data.

                            XML documents inserted into such a table/column are automatically shredded and data stored in that underlying structure.

                             

                            One important thing to retain is that although data is stored in a relational-like structure, accessing it directly is not supported by Oracle.

                            Data still has to be queried using the XML abstraction layer (XQuery).

                             

                            If you're interested in pursuing this approach, I encourage you to read the documentation first.

                            It covers the basics and much more :

                            http://docs.oracle.com/database/121/ADXDB/partpgschema.htm

                             

                            Here's also a complete example on a real use case :

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

                             

                            The XML DB forum is also there to discuss and ask questions on this topic.

                             

                            But as already said, you probably don't need to go down that route.

                            Depending on your version, you may just want to directly parse the document using XMLTABLE, or use an binary XML staging table if the document is large.

                            • 11. Re: Import XML data
                              lrs

                              I'm trying to run a simple example, but don't works...

                               

                              The XML file, placed in drive C:\ and named "example.xml":

                               

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

                              <customers>

                                <customer>

                                  <id>1</id>

                                <name>JOHN</name>

                                  <surname>SMITH</surname>

                                </customer>

                                <customer>

                                  <id>2</id>

                                <name>JACK</name>

                                  <surname>MILLER</surname>

                                </customer>

                              </customers>

                               

                               

                              The SQL Code (to import data):

                               

                              create table tab_customers (

                                   id integer primary key,

                                   name varchar2(20) not null,

                                   surname varchar2(20) not null);

                              commit;

                               

                              create or replace directory TMP_DIR as 'C:\';

                              insert into tab_customers (id, name, surname)

                              select x.id, x.name, x.surname

                              from xmltable('/customers/customer'

                                  passing xmltype(

                                      bfilename('TMP_DIR', 'example.xml'),

                                      nls_charset_id('AL32UTF8'))

                                  columns id integer path '@cid',

                                      name varchar2(20) path 'name',

                                      surname varchar2(20) path 'surname'

                                  ) x

                              ;

                               

                              The error:

                               

                              The system cannot find the file specified.

                              ORA-06512: en "SYS.XMLTYPE", línea 296

                              ORA-06512: en línea 1

                              22288. 00000 -  "file or LOB operation %s failed\n%s"

                              *Cause:    The operation attempted on the file or LOB failed.

                              *Action:   See the next error message in the error stack for more detailed

                                         information.  Also, verify that the file or LOB exists and that

                                         the necessary privileges are set for the specified operation. If

                                         the error still persists, report the error to the DBA.

                              • 12. Re: Import XML data
                                BluShadow

                                Is the C:\ directory referring to C:\ on the database server (assuming you are using a Windows server)?

                                 

                                Remember, PL/SQL runs as a process on the database server.  It cannot see, nor hack across the network, to access the local drives of a client computer.

                                The best way to imagine it is... if you went to the database server and logged on to it, could you access the file you want from that server?  If you can't... neither can the database.

                                • 13. Re: Import XML data
                                  lrs

                                  Done!

                                  Thanks everybody.

                                  • 14. Re: Import XML data
                                    lrs

                                    One more question.

                                     

                                    Suppoose the code to import data from "employees.xml" file is like below.

                                    Suppose "employees.xml" contains many fields within each XML element, for example 100 fields to import.

                                    Is there any way to import all data and create a table without having to declare each field one by one, in the column parameter?

                                     

                                    insert into tab_customers (id, name, surname)

                                    select x.id, x.name, x.surname

                                    from xmltable('/customers/customer'

                                        passing xmltype(

                                            bfilename('TMP_DIR', 'employees.xml'),

                                            nls_charset_id('AL32UTF8'))

                                        columns id integer path '@cid',

                                            name varchar2(20) path 'name',

                                            surname varchar2(20) path 'surname'

                                        ) x

                                    ;

                                    1 2 Previous Next