6 Replies Latest reply: Apr 12, 2012 12:26 AM by 929505 RSS

    How to parse XML Column and insert values into a table

    929505
      Hello,

      I am working on a simple project to demonstrate how to load and extract XML using SQL, I have already made a table that contains a column of XMLTYPE and loaded an XML file into it (code below)

      create or replace directory XMLSRC as 'C:\XMLSRC';
      drop table Inventory;
      create table Inventory(Inv XMLTYPE);
      INSERT INTO Inventory VALUES (XMLTYPE(bfilename('XMLSRC', 'Inventory.xml'),nls_charset_id('AL32UTF')));
      select * from Inventory;


      I now however need to get the XML data back out of that and loaded into a new table. Troubleshooting guides I have read online seem to only be dealing with parsing an external XML document and loading it into a table, and not what I need to do which is parse a column of XML data and load that into a table. The project trivial with simple tables containing only 3 columns.




      The table that needs to be loaded is as follows:


      create table InventoryOut(PartNumber Number(10), QTY Number(10), WhLocation varchar2(500));


      The XML document is as follows:

      <?xml version="1.0" encoding="UTF-8"?>
      <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Inventory.xsd" generated="2012-04-09T17:36:30">
      <Inventory>
      <PartNumber>101</PartNumber>
      <QTY>12</QTY>
      <WhLocation>WA</WhLocation>
      </Inventory>
      </dataroot>


      Thank you for any help you can offer.
        • 1. Re: How to parse XML Column and insert values into a table
          odie_63
          What's your database version?

          What you require is a most asked question, try searching the forum for terms like "XML to table", "load XML into Oracle", etc. You'll find plenty of examples.
          Troubleshooting guides I have read online seem to only be dealing with parsing an external XML document and loading it into a table
          Working from a table column instead is not that much different.
          What have you tried so far?
          • 2. Re: How to parse XML Column and insert values into a table
            929505
            Hi, the version is 11g, sorry about leaving that out.

            I tried a few different things, I didnt bother posting that part of the code in because I know its ugly :) I will post the two ways I have messed around with though below:


            drop table InventoryOut;

            create table InventoryOut(PartNumber Number(10), QTY Number(10), WhLocation varchar2(500));




            Try1:

            create or replace procedure put_stuff_into_table(source_xml_doc xmltype) AS

            BEGIN

            insert into table InventoryOut (PartNumber, QTY, WhLocation)
            select *
            from Inventory('<Inventory>'
            passing source_xml_doc
            columns PartNumber number path 'PartNumber',
            QTY number path 'QTY',
            WhLocation varchar2(500) path 'WhLocation'
            );
            END;

            select * from InventoryOut;



            Try2:

            insert into InventoryOut (PartNumber, QTY, WhLocation)
            (SELECT * from Inventory,
            XMLTABLE('/ROWSET' PASSING Inventory.Inv columns
            "PartNumber" number PATH 'ROWSET/ROW/PartNumber',
            "QTY" number PATH 'ROWSET/ROW/QTY',
            "WhLocation" varchar2(500) PATH 'ROWSET/ROW/WhLocation'));

            select * from InventoryOut;

            **This one I am unsure about the ROWSET paths, I found an example done like this online and am unsure if those paths need to be changed on a by application basis, I am unfamiliar with the idea of pathing in this way.




            The XML Document contains (again only one entry for now as I am just trying to get this to work)

            <?xml version="1.0" encoding="UTF-8"?>
            <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Inventory.xsd" generated="2012-04-09T17:36:30">
            <Inventory>
            <PartNumber>101</PartNumber>
            <QTY>12</QTY>
            <WhLocation>WA</WhLocation>
            </Inventory>
            </dataroot>




            Thanks
            • 3. Re: How to parse XML Column and insert values into a table
              Jason_(A_Non)
              11g is a marketing label for all the release versions that start with 11. One method to find your version is
              select * from v$version.

              Here is what you are probably looking for
              Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
               
              SQL> 
              SQL> create table Inventory(Inv XMLTYPE)
                2  xmltype column Inv store as securefile binary xml;
               
              Table created
              SQL> -- If 11.2.0.2 or greater the second line in the CREATE TABLE is not needed as securefile binary is the default
              SQL> -- too lazy to load small XML via BFILENAME.  Added second Inventory item
              SQL> INSERT INTO Inventory VALUES (XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
                2  <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Inventory.xsd" generated="2012-04-09T17:36:30">
                3  <Inventory>
                4  <PartNumber>101</PartNumber>
                5  <QTY>12</QTY>
                6  <WhLocation>WA</WhLocation>
                7  </Inventory>
                8  <Inventory>
                9  <PartNumber>102</PartNumber>
               10  <QTY>32</QTY>
               11  <WhLocation>MT</WhLocation>
               12  </Inventory>
               13  </dataroot>'));
               
              1 row inserted
              SQL> create table InventoryOut(PartNumber Number(10), QTY Number(10), WhLocation varchar2(500));
               
              Table created
               
              SQL> 
              SQL> insert into InventoryOut (PartNumber, QTY, WhLocation)
                2  select xt.*
                3  from Inventory inve,
                4       XMLTable('/dataroot/Inventory'
                5                PASSING inve.Inv
                6                COLUMNS
                7                PartNumber number        path 'PartNumber',
                8                QTY        number        path 'QTY',
                9                WhLocation varchar2(500) path 'WhLocation') xt;
               
              2 rows inserted
              SQL> select * from InventoryOut;
               
               PARTNUMBER         QTY WHLOCATION
              ----------- ----------- --------------------------------------------------------------------------------
                      101          12 WA
                      102          32 MT
              As you can see, you were close in concept to what you needed. To answer the ROWSET/ROW question, you were looking at a common XML representation of data from a table. Many tools extract in that format when pulling data out into XML format. ROWSET is the root node of the XML and ROW exists for every row in the table/result set. Within ROW, each node name is the name of a column from the table and the contents of that node is the content of the column from the table.

              As your XML is formatted differently, you simply needed to use the XPaths relevant to your XML. Hope that helps you continue on.
              • 4. Re: How to parse XML Column and insert values into a table
                929505
                First of all, thank you for your help!! Still stunned that you actually took the time to write out an eample using my tables/names/etc. Thank you!!

                Attached is the code, there seems to be an issue with referencing the other table, Inventory.Inv, I checked and that table and the Inv column are showing up in the database so I am not sure why it is having issues locating them, take a look at the code I wrote as well as the output (*I included the real version number for you this time :)

                EDIT: In your code right here:
                select xt.*
                3 from Inventory inve,
                4 XMLTable('/dataroot/Inventory'
                5 PASSING inve.Inv

                I think is where I am messing it up, perhaps not understanding fully what is going on, as you write "Inventory inve" and "inve.Inv" ---- Is inve a keyword that I am just not familiar with? I think this is where the issues lies in my code.
                END EDIT

                EDIT2: Well that looks like it was it, changed that to how you have it and it now works!!! Could you please explain what that few lines is doing, and what the xt.* and inve are doing? Thanks again!!!
                END EDIT2




                drop table InventoryOut;
                create table InventoryOut (PartNumber number(10), QTY number(10), WhLocation varchar2(500));

                insert into InventoryOut (PartNumber, QTY, WhLocation)
                select xt.*
                from Inventory Inv,
                XMLTable('/dataroot/Inventory'
                PASSING Inventory.Inv COLUMNS
                PartNumber number path 'PartNumber',
                QTY number path 'QTY',
                WhLocation path 'WhLocation')xt;


                select * from InventoryOut;
                select * from v$version;






                table INVENTORYOUT dropped.
                table INVENTORYOUT created.

                Error starting at line 4 in command:
                insert into InventoryOut (PartNumber, QTY, WhLocation)
                select xt.*
                from Inventory Inv,
                XMLTable('/dataroot/Inventory'
                PASSING Inventory.Inv COLUMNS
                PartNumber number path 'PartNumber',
                QTY number path 'QTY',
                WhLocation path 'WhLocation')xt
                Error at Command Line:8 Column:12
                Error report:
                SQL Error: ORA-00904: "INVENTORY"."INV": invalid identifier
                00904. 00000 - "%s: invalid identifier"
                *Cause:   
                *Action:
                PARTNUMBER QTY WHLOCATION
                ---------------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                PL/SQL Release 11.2.0.1.0 - Production
                CORE     11.2.0.1.0     Production
                TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                NLSRTL Version 11.2.0.1.0 - Production









                If this helps here is the code and output for the creation of the Inventory table itself:

                create or replace directory XMLSRC as 'C:\XMLSRC';
                drop table Inventory;
                create table Inventory(Inv XMLTYPE);
                INSERT INTO Inventory VALUES (XMLTYPE(bfilename('XMLSRC', 'Inventory.xml'),nls_charset_id('AL32UTF')));
                select * from Inventory;




                directory XMLSRC created.
                table INVENTORY dropped.
                table INVENTORY created.
                1 rows inserted.
                INV
                --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                <?xml version="1.0" encoding="WINDOWS-1252"?>
                <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Inventory.xsd" generated="2012-04-09T17:36:30">
                <Inventory>
                <PartNumber>101</PartNumber>
                <QTY>12</QTY>
                <WhLocation>WA</WhLocation>
                </Inventory>
                </dataroot>




                Thanks again for your help so far! Hope we can get this working :)

                Edited by: 926502 on Apr 11, 2012 2:47 PM

                Edited by: 926502 on Apr 11, 2012 2:49 PM

                Edited by: 926502 on Apr 11, 2012 2:54 PM

                Edited by: 926502 on Apr 11, 2012 2:54 PM

                Updated issue to solved - Edited by: 926502 on Apr 11, 2012 2:55 PM
                • 5. Re: How to parse XML Column and insert values into a table
                  Jason_(A_Non)
                  That's why we like it when people provide samples as it means we don't have to make up something that probably looks nothing like what you are working with and trying to understand.

                  I forgot to pass on a formatting tip: Look in the FAQ, found via https://wikis.oracle.com/display/Forums/Forums+FAQ at how to use the { code } tag (without the spaces) to retain formatting like I did.
                  from Inventory inve
                  A simple case of using
                  from Table Alias
                  PASSING inve.Inv
                  simply using the Alias name so Oracle would know what table to find the data in (in this case).

                  I could have simply done
                  from Inventory,
                       XMLTable('/dataroot/Inventory'
                                PASSING inventory.Inv
                  so that Oracle knew to use the XML data in Inventory.Inv for parsing.

                  Also, when creating your table, make sure to use the second line
                  xmltype column Inv store as securefile binary xml
                  that I did. This changes the storage model for how the XML is stored internally. If the XML inserted into that table grows in volume, you will notice a performance impact between the way you create the table and the way I did. Storing it as binary XML will improve your performance when querying the data.
                  • 6. Re: How to parse XML Column and insert values into a table
                    929505
                    OK that might explain why the file (when transferred back into XML) is bloating itself with 10-12 spare lines of white space :) I will also give that a shot. Thank you!