10 Replies Latest reply: Feb 6, 2006 4:52 AM by mdrake-Oracle RSS

    How to use PL/SQL to read and manipulate data from a xml file

    487416
      Hi,

      I have a XML file and i need to read data on the file to manipulate it and then insert in a oracle table (not with xmlType attributes). I just need to be able to access xml nodes in the file and manipulate data of the nodes. The thing is that i need to do this with a pl/sql procedure compiled in the Database, version 9.2.07.

      Can someone give a good orientation on how to do this? I'm totally lost here and have lot of urgency.

      I've seen examples of reading the xml file with BFILE, but i do not have sysDBA permissions on the database and failed to do it.


      Thanks in advance
        • 1. Re: How to use PL/SQL to read and manipulate data from a xml file
          mdrake-Oracle
          The easiest way to do this is to register an XML Schema that describes your XML, insert your XML into the tables created by the schema registration process and then create relational views that expose the content of the XML as a series of relational views. If you search for "relational view" in this forum you should find plenty of examples. The technique is also explained in the XML DB standard demo which can be downloaded from the XML DB OTN page.

          http://www.oracle.com/technology/tech/xml/xmldb/index.html
          • 2. Re: How to use PL/SQL to read and manipulate data from a xml file
            487416
            Ok, i will try to search it that way, but

            assuming i don't have a xml schema for my xml file, since the file is created by me in a Excel application, is there any way around? The only thing i need is to read data from the file using a PL/SQL procedure that receives the file name as a parameter.


            Thanks
            • 3. Re: How to use PL/SQL to read and manipulate data from a xml file
              487534
              Hi,

              I have a similar problem.
              We have Oracle 9.2.0.7 here ...
              I started reading documentation on XML DB and started to try out some examples. I can create a table with a column of xmltype. I finally even inserted a small xml block into the table. I just don't know how I can access the XML and parse it.
              When I do a select I only get "(ORAXML)" as a result ... ?!

              Do I need to have anything additional installed to get it to work?

              Regards,

              Steff
              • 4. Re: How to use PL/SQL to read and manipulate data from a xml file
                mdrake-Oracle
                Steff

                What are you using to do the select. It doesn't sound like it's SQL*PLUS. If it's a tool it has to be using an OCI library from 9.2.0.3.0 or later and possibly has to be updated to understand XMLType. Can you try from SQL*PLUS

                You can build a DOM from you xmltype using DBMS_XMLDOM.NEWDOMDOCUMENT(XMLTYPE)

                You can also work with your XMLType at a higher level, using XPath expressions to access fragments and scalar values using the SQL extract() and extractValue operators. A good tuturiol for this approach is the XML DB standard demo which can be found on the XML DB home page...

                http://www.oracle.com/technology/tech/xml/xmldb/index.html

                Since you are on 9.2.x you'll probably want to get the 9.2.x specific version of the demo from here

                http://www.oracle.com/technology/tech/xml/xmldb/9.2.0.2.0/index.html
                • 5. Re: How to use PL/SQL to read and manipulate data from a xml file
                  mdrake-Oracle
                  Ok, i will try to search it that way, but

                  assuming i don't have a xml schema for my xml file,
                  since the file is created by me in a Excel
                  application, is there any way around? The only thing
                  i need is to read data from the file using a PL/SQL
                  procedure that receives the file name as a
                  parameter.


                  Thanks
                  You can use the operators without an XML Schema. You can use DBMS_XMLDOM package. Both will be much less efficient than using an XMLSchema. THere are a number of tools, including Altova's XMLSPy that can easily reverse engineer an XML Schema from an example instance document.
                  • 6. Re: How to use PL/SQL to read and manipulate data from a xml file
                    487416
                    Ok, things just got a little more interesting, it seems that now i don't have a xml file, i have to find a way to pass a xml type structure from a vba sub to a pl/sql procedure who receives it as a parameter. After that i have to use pl/sql to manipulate data from that structure.
                    Does anyone have an idea on how to do it? And where to find info about it?
                    • 7. Re: How to use PL/SQL to read and manipulate data from a xml file
                      487534
                      Hi,

                      I use Toad to access the database. You were right, if I use SQL+ I can see the content. However, the content seems to be chopped off.
                      Don't know if that is SQL+ fault or if data has been lost as well.

                      As for the example, it is hard for me to use since I can't install anything here.
                      So I can only use the PDF, but since it is based on the tools installed it is hard to try things out.

                      How do I build a DOM with DBMS_XMLDOM.NEWDOMDOCUMENT(XMLTYPE)? Must I use the function when I am inserting the data into the table?

                      Can I loop through the nested elements within XML data?

                      Thanks,

                      Stefanie
                      • 8. Re: How to use PL/SQL to read and manipulate data from a xml file
                        mdrake-Oracle
                        User error I'm afraid..

                        By default I think SQL*PLUS prints the first 100 or so characters of 'long' data types (CLOB, XMLTYPE). You can addjust this setting using the set long nnnnnn
                        comand, where nnnnn is the amound of data you want sqlplus to print.

                        DBMS_XMLDOM implements the W3C DOM API Standard.The DOM API lets you walk up and down the nodes in an XML document, iterate over collections of nodes and access the values of the nodes. You will have to manually code the insert statements required to store data in the DOM structure into your tables. DOM Programming is fairly simple but can be very verbose.

                        This is where the alternative of using an XML Schema and then creating relational views can reduce the amount of coding effort by serveral orders of magnitude.
                        • 9. Re: How to use PL/SQL to read and manipulate data from a xml file
                          487534
                          Hi,

                          thanks for the reply ...

                          you don't happen to have small and simple examples for both variants that would help me to get on the right way, would you?

                          Thanks a lot,

                          Steffi
                          • 10. Re: How to use PL/SQL to read and manipulate data from a xml file
                            mdrake-Oracle
                            The standard demo shows the XML Schema route.

                            The following code shows the DOM route...
                            declare
                              SCHEMALIST_XML   XMLTYPE                     := XMLType(bfilename(USER,'&3'),nls_charset_id('AL32UTF8'));
                              
                              SCHEMALIST_DOM   DBMS_XMLDOM.DOMDOCUMENT;
                              SCHEMA_NODELIST  DBMS_XMLDOM.DOMNODELIST;
                              SCHEMA_NODE      DBMS_XMLDOM.DOMNODE;
                             
                              SOURCE_PATH      VARCHAR2(256);
                              SCHEMA_URL       VARCHAR2(256);
                            
                              NODE_VALUE       VARCHAR2(256);
                            
                              LOCAL            BOOLEAN := TRUE;
                              GENTYPES         BOOLEAN := TRUE;
                              GENBEAN          BOOLEAN := FALSE;
                              GENTABLES        BOOLEAN := TRUE;
                              FORCE            BOOLEAN := FALSE;
                            
                              OWNER            VARCHAR2(32) := USER;
                             
                              SCHEMA_XPATH     VARCHAR2(256);
                              CHILD_XPATH      VARCHAR2(256);
                              
                              XPATH_INDEX      number(2);
                            
                              XMLSCHEMA xmltype;
                            
                            begin
                              SCHEMALIST_DOM   := DBMS_XMLDOM.newDOMDocument(SCHEMALIST_XML);
                              SCHEMA_NODELIST  := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(SCHEMALIST_DOM,'schema');
                              
                              FOR i in 0 .. (DBMS_XMLDOM.GETLENGTH(SCHEMA_NODELIST) - 1) LOOP
                            
                                LOCAL       := TRUE;
                                GENTYPES    := TRUE;
                                GENBEAN     := FALSE;
                                GENTABLES   := TRUE;
                                FORCE       := FALSE;
                                OWNER       := USER;
                                
                                XPATH_INDEX := i+1;
                            
                                SCHEMA_XPATH   := '/schemas/schema[' || XPATH_INDEX || ']/';
                            
                                CHILD_XPATH    := SCHEMA_XPATH || 'document/text()';
                                DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.MAKENODE(SCHEMALIST_DOM),CHILD_XPATH, SOURCE_PATH);
                            
                                CHILD_XPATH    := SCHEMA_XPATH || 'schemaURL/text()';
                                DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.MAKENODE(SCHEMALIST_DOM),CHILD_XPATH, SCHEMA_URL);
                            
                                CHILD_XPATH    := SCHEMA_XPATH || 'owner/text()';
                                DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.MAKENODE(SCHEMALIST_DOM),CHILD_XPATH, NODE_VALUE);
                                if (UPPER(NODE_VALUE) IS NOT NULL) then
                                  OWNER := UPPER(NODE_VALUE);
                                end if;
                            
                                CHILD_XPATH    := SCHEMA_XPATH || 'local/text()';
                                DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.MAKENODE(SCHEMALIST_DOM),CHILD_XPATH, NODE_VALUE);
                                if (UPPER(NODE_VALUE) = 'FALSE') then
                                  LOCAL := FALSE;
                                end if;
                                
                                CHILD_XPATH    := SCHEMA_XPATH || 'genTypes/text()';
                                DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.MAKENODE(SCHEMALIST_DOM),CHILD_XPATH, NODE_VALUE);
                                if (UPPER(NODE_VALUE) = 'FALSE') then
                                  GENTYPES := FALSE;
                                end if;
                            
                                CHILD_XPATH    := SCHEMA_XPATH || 'genTables/text()';
                                DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.MAKENODE(SCHEMALIST_DOM),CHILD_XPATH, NODE_VALUE);
                                if (UPPER(NODE_VALUE) = 'FALSE') then
                                  GENTABLES := FALSE;
                                end if;
                            
                                CHILD_XPATH    := SCHEMA_XPATH || 'force/text()';
                                DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.MAKENODE(SCHEMALIST_DOM),CHILD_XPATH, NODE_VALUE);
                                if (UPPER(NODE_VALUE) = 'TRUE') then
                                  FORCE := TRUE;
                                end if;
                                
                                dbms_output.put_Line('dbms_xmlschema.registerSchema');
                                dbms_output.put_Line('(');
                                dbms_output.put_line('SCHEMAURL => "' || SCHEMA_URL  || '",');
                                dbms_output.put_line('SCHEMADOC => "' || SOURCE_PATH || '",');
                                dbms_output.put_line('LOCAL => '      || xdb_dom_helper.boolean_to_varchar(LOCAL)    || ',');
                                dbms_output.put_line('GENTYPES => '   || xdb_dom_helper.boolean_to_varchar(GENTYPES) || ',');
                                dbms_output.put_line('GENBEAN  => '   || xdb_dom_helper.boolean_to_varchar(GENBEAN)  || ',');
                                dbms_output.put_line('GENTABLES => '  || xdb_dom_helper.boolean_to_varchar(GENTABLES)|| ',');
                                dbms_output.put_line('FORCE => '      || xdb_dom_helper.boolean_to_varchar(FORCE)    || ',');
                                dbms_output.put_line('OWNER => "'     || OWNER       || '"');
                                dbms_output.put_line(')');
                            
                                insert statement goes here..
                             
                            end;
                            /
                            The above processes a very simple XML document in the following form
                            <schemas>
                              <schema>
                                <document>/home/XBRL/lib/xlink-2003-12-31.xsd</document>
                                <schemaURL>http://www.xbrl.org/2003/xlink-2003-12-31.xsd</schemaURL>
                                <genTables>false</genTables>
                              </schema>
                              <schema>
                                <document>/home/XBRL/lib/xl-2003-12-31.xsd</document>
                                <schemaURL>http://www.xbrl.org/2003/xl-2003-12-31.xsd</schemaURL>
                                <genTables>false</genTables>
                              </schema>
                              <schema>
                                <document>/home/XBRL/lib/xbrl-linkbase-2003-12-31.xsd</document>
                                <schemaURL>http://www.xbrl.org/2003/xbrl-linkbase-2003-12-31.xsd</schemaURL>
                                <genTables>true</genTables>
                              </schema>
                              <schema>
                                <document>/home/XBRL/lib/xbrl-instance-2003-12-31.xsd</document>
                                <schemaURL>http://www.xbrl.org/2003/xbrl-instance-2003-12-31.xsd</schemaURL>
                                <genTables>true</genTables>
                              </schema>
                            </schemas>