1 2 Previous Next 16 Replies Latest reply: Oct 25, 2012 9:37 AM by 964494 RSS

    Need to insert values into a table from a XML file

    964494
      Hi,
      I'm an Oracle 9i/10g DBA with quite a few years experience, but I'm new to XML and dealing with it in database terms. I've been given a project that entails pulling XML values out of a file (or 100's of them) and storing them in the database so that they are searchable by end-users. The project is classified as secret so I'm unable to upload the specific XML or any info relating to the structire of the XML or the table I will use to insert the values into - sorry!! So, I've created an XML file with a similar structure to help people understand my predicament.

      The end-users only need to search on a subset of the total amount of columns from the table I'll insert data into, although the XML file has a lot more, so I dont need to store the other values - but I will need to store the name of the XML file (or a pointer to it so I know what XML file a particular set of values belong to) in another column of the table along with its associated values.

      I've been using the XMLTABLE function with some degree of success, although I had better succes using the XMLSEQUENCE function. However, I found out this is deprecated in 10g and replaced with XMLTABLE, so I guess it's better if I use this in case we ever need to upgrade to 11g.

      The main problem I've been having is that some elements in the XML files have multiple values for the one record when all the other records are the same. In terms of storing this in the database, I guess it would mean inserting multiple rows in the table for each element where the value differs. Here is a dumbed down XML file similar to what I've got along with the other SQL I've used:


      +<?xml version="1.0" encoding="UTF-8"?>+
      +<House>+
      +<Warehouse>+
      +<WarehouseId>1</WarehouseId>+
      +<WarehouseName>+
      +<Town>Southlake</Town>+
      +<State>Texas</State>+
      +</WarehouseName>+
      +<Building>Owned</Building>+
      +<Area>25000</Area>+
      +<Docks>2</Docks>+
      +<DockType>Rear load</DockType>+
      +<WaterAccess>true</WaterAccess>+
      +<RailAccess>N</RailAccess>+
      +<Parking>Street</Parking>+
      +<VClearance>10</VClearance>+
      +</Warehouse>+
      +<Warehouse>+
      +<WarehouseId>2</WarehouseId>+
      +<WarehouseName>+
      +<Town>Poole</Town>+
      +<State>Dorset</State>+
      +</WarehouseName>+
      +<WarehouseName>+
      +<Town>Solihull</Town>+
      +<County>West Midlands</State>+
      +</WarehouseName>+
      +<Building>Owned</Building>+
      +<Area>40000</Area>+
      +<Docks>5</Docks>+
      +<DockType>Rear load</DockType>+
      +<WaterAccess>true</WaterAccess>+
      +<RailAccess>N</RailAccess>+
      +<Parking>Bay</Parking>+
      +<VClearance>10</VClearance>+
      +</Warehouse>+
      +<Warehouse>+
      +<WarehouseId>3</WarehouseId>+
      +<WarehouseName>+
      +<Town>Fleet</Town>+
      +<County>Hampshire</County>+
      +</WarehouseName>+
      +<Building>Owned</Building>+
      +<Area>10000</Area>+
      +<Docks>1</Docks>+
      +<DockType>Side load</DockType>+
      +<WaterAccess>false</WaterAccess>+
      +<RailAccess>N</RailAccess>+
      +<Parking>Bay</Parking>+
      +<VClearance>20</VClearance>+
      +</Warehouse>+
      +</House>+


      CREATE TABLE xmltest OF XMLTYPE;

      INSERT INTO xmltest
      VALUES(xmltype(bfilename('XML_DIR', 'test.xml'), nls_charset_id('AL32UTF8')));


      Consequently, I need to...
      1) Retrieve the results from the XML file for all 3 warehouses where multiple values for the same sub-element are shown as 2 rowsthe result set. (I am guessing there will be 4 rows returned as warehouse sub-2 has 2 different elements for <WarehouseName>.
      2) Build a case statement into the query so that regardless of the sub-element name (i.e State or County), it is returned into the 1 column, for instance County.

      So, if I run a query similar to the following...

      select y.WarehouseId, y.Town, y.County, y.Area
      from xmltest x, xmltable('/House/Warehouse' .......

      I would like to get results back like this...

      ID Town County Area
      1 Southlake Texas 25000
      2 Poole Dorset 40000
      2 Solihull West Midlands 40000
      3 Fleet hampshire 10000

      Sorry for the non-formatting but I hope this all makessense to someone out there with what I'm trying to do.

      I appreciate any help whatsoever because, as i said before, I'm totally new to XML and trying to read the vast amount of information there is out there on XML is all a bit daunting.

      Many thanks in advance,
      Shaun.
        • 1. Re: Need to insert values into a table from a XML file
          odie_63
          Hi,
          The project is classified as secret so I'm unable to upload the specific XML or any info relating to the structire of the XML or the table I will use to insert the values into - sorry!! So, I've created an XML file with a similar structure to help people understand my predicament.
          No problem. As long as the structure mirrors the real one, and is wellformed...
          ...
          <County>West Midlands</State>
          ...
          Sorry for the non-formatting
          Use &#x7b;code} tags to enclose code snippets you want to preserve formatting.


          Here are some options :

          10g (tested on 10.2.0.5) :
          SQL> select x1.wid
            2       , x2.town
            3       , nvl(x2.county, x2.state) as county
            4       , x1.area
            5  from tmp_xml t
            6     , xmltable('/House/Warehouse'
            7         passing t.object_value
            8         columns wid     number  path 'WarehouseId'
            9               , area    number  path 'Area'
           10               , wnames  xmltype path 'WarehouseName'
           11       ) x1
           12     , xmltable('/WarehouseName'
           13         passing x1.wnames
           14         columns town    varchar2(30) path 'Town'
           15               , county  varchar2(30) path 'County'
           16               , state   varchar2(30) path 'State'
           17       ) x2
           18  ;
           
                 WID TOWN                           COUNTY                               AREA
          ---------- ------------------------------ ------------------------------ ----------
                   1 Southlake                      Texas                               25000
                   2 Poole                          Dorset                              40000
                   2 Solihull                       West Midlands                       40000
                   3 Fleet                          Hampshire                           10000
           
          SQL> 
          SQL> select x1.wid
            2       , x2.town
            3       , x2.county
            4       , x1.area
            5  from tmp_xml t
            6     , xmltable('/House/Warehouse'
            7         passing t.object_value
            8         columns wid     number  path 'WarehouseId'
            9               , area    number  path 'Area'
           10               , wnames  xmltype path 'WarehouseName'
           11       ) x1
           12     , xmltable(
           13         'for $i in /WarehouseName
           14          return element r {
           15            $i/Town
           16          , element County { data($i/(County|State)) }
           17          }'
           18         passing x1.wnames
           19         columns town    varchar2(30) path 'Town'
           20               , county  varchar2(30) path 'County'
           21       ) x2
           22  ;
           
                 WID TOWN                           COUNTY                               AREA
          ---------- ------------------------------ ------------------------------ ----------
                   1 Southlake                      Texas                               25000
                   2 Poole                          Dorset                              40000
                   2 Solihull                       West Midlands                       40000
                   3 Fleet                          Hampshire                           10000
           
          11g (11.2.0.2), looks like the union operator in the PATH clause now works correctly :
          SQL> select x1.wid
            2       , x2.town
            3       , x2.county
            4       , x1.area
            5  from tmp_xml t
            6     , xmltable('/House/Warehouse'
            7         passing t.object_value
            8         columns wid     number  path 'WarehouseId'
            9               , area    number  path 'Area'
           10               , wnames  xmltype path 'WarehouseName'
           11       ) x1
           12     , xmltable('/WarehouseName'
           13         passing x1.wnames
           14         columns town    varchar2(30) path 'Town'
           15               , county  varchar2(30) path 'County|State'
           16       ) x2
           17  ;
           
                 WID TOWN                           COUNTY                               AREA
          ---------- ------------------------------ ------------------------------ ----------
                   1 Southlake                      Texas                               25000
                   2 Poole                          Dorset                              40000
                   2 Solihull                       West Midlands                       40000
                   3 Fleet                          Hampshire                           10000
           
          • 2. Re: Need to insert values into a table from a XML file
            Jason_(A_Non)
            Just to expand on Odie's examples, OBJECT_VALUE is a pseudocolumn as listed by the Oracle documentation for [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns006.htm#sthref822]OBJECT_VALUE.
            11g (11.2.0.2), looks like the union operator in the PATH clause now works correctly :
            Confirmed works in 11.1.0.6 as well.
            • 3. Re: Need to insert values into a table from a XML file
              odie_63
              11g (11.2.0.2), looks like the union operator in the PATH clause now works correctly :
              Confirmed works in 11.1.0.6 as well.
              Thanks for checking Jason.

              On 10g, the query doesn't produce any error, the second operand is simply ignored, resulting in a NULL column when the 2nd element is present instead of the 1st.
              Yet another "undocumented feature" ;)
              • 4. Re: Need to insert values into a table from a XML file
                964494
                Odie / Jason,

                Thank you both for the prompt replies. Whatever you're getting paid, it's not enough!!

                I've been playing with those scripts this morning and they've been a great help. I've had to change it a little to get it to return the data as I'd expect though and I wanted to run a couple of things by you to make sure I'm doing it correctly.

                I've changed the PATH structure so that it includes the parent elements as well. This is because I later realised after my inital post that one of the elements was at a different level (had another sub-element if that's the correct term) in it, as can be seen below:

                SQL> select x1.wid
                2 , x2.town
                3 , nvl(x2.county, x2.state) as county
                4 , x1.area
                5 from tmp_xml t
                6 , xmltable('/House/Warehouse'
                7 passing t.object_value
                8 columns wid number path 'Warehouse/WarehouseId'
                9 , area number path 'Warehouse/Country/Area'
                10 , wnames xmltype path 'Warehouse/WarehouseName'
                11 ) x1
                12 , xmltable('/WarehouseName'
                13 passing x1.wnames
                14 columns town varchar2(30) path 'Town'
                15 , county varchar2(30) path 'County'
                16 , state varchar2(30) path 'State'
                17 ) x2
                18 ;

                It seems to work okay, but i wanted to check with you guys that it's correct syntactically.

                Something else that I need to overcome (which I've only found out afterwards when my manager told me the XML file I was dealing with is a cut-version of the XMl file I'll actually have to insert) is that the files that are being dumped on the OS seem to contain multiple messages in the form:

                <xml version="1.0"?>
                <!DOCTYPE CMF_Hdr "CMF_Doc.dtd">
                <CMF_Hdr>
                ........
                </CMF_Hdr>
                <xml version="1.0"? !DOCTYPE CMF_Hdr "CMF_Doc.dtd">
                <CMF_Doc>
                ........
                </CMF_Doc>
                <xml version="1.0"?>
                <!DOCTYPE CMF_Hdr "CMF_Doc.dtd">
                <CMF_Hdr>
                ........
                </CMF_Hdr>
                <xml version="1.0"? !DOCTYPE CMF_Hdr "CMF_Doc.dtd">
                <CMF_Doc>
                ........
                </CMF_Doc>


                So, in the 1 file there are separate XML messages, and when I try and insert this file into the database, it errors with ORA-31001: invalid resource handle path name "CMF_Doc.dtd". I guess that it's trying to insert the XML file based on a definition file (which we don't have but we're trying to get hold of). I've managed to overcome this issue by by executing the following:

                alter session set events=’31156 trace name context forever, level 2 forever’;

                I also guess that it doesn't like the fact I'm trying to insert multiple XML messages from the 1 file. So I guess I have a few questions at this point:

                1) Is the alter session statement the correct thing to do at this point?
                2) Is it worth trying to get hold of the definition file and will it make a difference to how I go about this whole project?
                3) Is there a way or breaking up the 1 large file into separate XML messages that can then be inserted, or can I insert it 'as is' somehow?

                Sorry for the long description and more questions, but I really appreciate any help you guys can give me.

                Thanks,
                Shaun.

                P.S. How do you do the formatting on this website so your script is formatted properly? I know you said with tags, but I can't seem to get it to work.
                • 5. Re: Need to insert values into a table from a XML file
                  odie_63
                  I've changed the PATH structure so that it includes the parent elements as well. This is because I later realised after my inital post that one of the elements was at a different level (had another sub-element if that's the correct term) in it, as can be seen below:

                  [...]
                  It seems to work okay, but i wanted to check with you guys that it's correct syntactically.
                  Does this query really work?
                  If you're extracting /House/Warehouse elements in the main XQuery expression, then Warehouse/WarehouseId (in the PATH clause) will point to nothing, because the path will be resolved as /House/Warehouse/Warehouse/WarehouseId.

                  Otherwise, it seems OK.

                  1) Is the alter session statement the correct thing to do at this point?
                  Point 3 is actually the main issue here.

                  Depending on point 2, the ALTER SESSION is the correct thing to do though... once point 3 gets resolved.
                  2) Is it worth trying to get hold of the definition file and will it make a difference to how I go about this whole project?
                  Depends.
                  Do you want to validate each XML document as per the DTD?
                  If you don't, then don't bother retrieving it, and use the ALTER SESSION call.
                  3) Is there a way or breaking up the 1 large file into separate XML messages that can then be inserted, or can I insert it 'as is' somehow?
                  Yes, you can do it with PL/SQL and DBMS_LOB package.
                  Here a similar thread dealing with this situation :
                  {thread:id=2394980}
                  • 6. Re: Need to insert values into a table from a XML file
                    odie_63
                    P.S. How do you do the formatting on this website so your script is formatted properly? I know you said with tags, but I can't seem to get it to work.
                    Explained here : https://forums.oracle.com/forums/help.jspa
                    and here : {message:id=9360002} (point #9)

                    Alternatively, you can reply to any post that shows formatting code, quote it and see how it is done (and cancel at the end if don't actually want to reply).
                    • 7. Re: Need to insert values into a table from a XML file
                      964494
                      Hi guys,

                      One other thing I forgot to mention is that the element Warehousename can contain any number of 1-5 sub-elements(nodes?), such as 'State or County'. In some XML extracts, it may only mention 1 sub-element, for example State. In other XML extracts, they may be more, such as State, County, Region, District etc.

                      Ideally, I want it to display 1 line for each sub-element if there is more than 1 (like you have done). However, I don't think the 'nvl' function will work as that only works 2 expressions (I believe), i.e NVL(expr1, expr2)

                      Is there something else I can use within this statement that will account for more than 2 sub-elements?

                      Hope you understand what I mean ?

                      Thanks,
                      Shaun.

                      P.S I will be away from my desk until next Wednesday so please don't close this thread, as I will definitely pick it up when I return.
                      • 8. Re: Need to insert values into a table from a XML file
                        Jason_(A_Non)
                        I think you are looking for [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm#SQLRF00617]COALESCE as a replacement for NVL when you have more than two items. If that is not what you were saying, then just create an example to help clarify your request.
                        • 9. Re: Need to insert values into a table from a XML file
                          964494
                          Hi Odie,

                          Thanks for the quick reply once again.

                          Sorry, I forgot to remove 'Warehouse' from the main XQuery expression when uploading it to this forum. it should have read /House

                          I don't need to validate each XML file because it would have been validated prior to me receiving it, so I'll guess I'll just encorporate the 'alter session' statement in my script.

                          Thanks for the link to help me split the XML up - I'll take a look.

                          Shaun.
                          • 10. Re: Need to insert values into a table from a XML file
                            odie_63
                            Sorry, I forgot to remove 'Warehouse' from the main XQuery expression when uploading it to this forum. it should have read /House
                            On the contrary, if <Warehouse> is the repeating element, then keep it in the main XQuery. The one from the PATH clause is to be removed.

                            Do not hesitate to post again a correct XML structure reflecting your data, it'll help clearing things up.

                            Edited by: odie_63 on 27 sept. 2012 16:32
                            • 11. Re: Need to insert values into a table from a XML file
                              964494
                              Hi again,

                              Thanks for keeping the post open for me. I've had a look at the post illustrating the XFileHandler package, and tried to alter it to make it fit with my XML files. To help explain things, my XML file looks like this:
                              <?xml version="1.0"?>
                              <!DOCTYPE  CMF_Doc SYSTEM "CMF_Doc.dtd">
                               <House>
                                  <Warehouse>
                                  <WarehouseId>1</WarehouseId>
                                  <WarehouseName>
                                     <Town>Southlake</Town>
                                     <State>Texas</State>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>25000</Area>
                                  <Docks>2</Docks>
                                  <DockType>Rear load</DockType>
                                  <WaterAccess>true</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Street</Parking>
                                  <VClearance>10</VClearance>
                                </Warehouse>
                                <Warehouse>House
                                  <WarehouseId>2</WarehouseId>
                                  <WarehouseName>
                                     <Town>Poole</Town>
                                     <State>Dorset</State>
                                  </WarehouseName>
                                  <WarehouseName>
                                     <Town>Solihull</Town>
                                     <County>West Midlands</County>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>40000</Area>
                                  <Docks>5</Docks>
                                  <DockType>Rear load</DockType>
                                  <WaterAccess>true</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Bay</Parking>
                                  <VClearance>10</VClearance>
                                </Warehouse>
                                <Warehouse>
                                  <WarehouseId>3</WarehouseId>
                                  <WarehouseName>
                                     <Town>Fleet</Town>
                                     <County>Hampshire</County>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>10000</Area>
                                  <Docks>1</Docks>
                                  <DockType>Side load</DockType>
                                  <WaterAccess>false</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Bay</Parking>
                                  <VClearance>20</VClearance>
                                </Warehouse>
                               </House>
                              <?xml version="1.0" encoding="UTF-8"?>
                               <House>
                                  <Warehouse>
                                  <WarehouseId>4</WarehouseId>
                                  <WarehouseName>
                                     <Town>Dallas</Town>
                                     <State>Texas</State>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>25000</Area>
                                  <Docks>2</Docks>
                                  <DockType>Rear load</DockType>
                                  <WaterAccess>true</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Street</Parking>
                                  <VClearance>10</VClearance>
                                </Warehouse>
                                <Warehouse>
                                  <WarehouseId>5</WarehouseId>
                                  <WarehouseName>
                                     <Town>Dorchester</Town>
                                     <State>Dorset</State>
                                  </WarehouseName>
                                  <WarehouseName>
                                     <Town>Solihull</Town>
                                     <County>West Midlands</County>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>40000</Area>
                                  <Docks>5</Docks>
                                  <DockType>Rear load</DockType>
                                  <WaterAccess>true</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Bay</Parking>
                                  <VClearance>10</VClearance>
                                </Warehouse>
                                <Warehouse>
                                  <WarehouseId>6</WarehouseId>
                                  <WarehouseName>
                                     <Town>Farnborough</Town>
                                     <County>Hampshire</County>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>10000</Area>
                                  <Docks>1</Docks>
                                  <DockType>Side load</DockType>
                                  <WaterAccess>false</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Bay</Parking>
                                  <VClearance>20</VClearance>
                                </Warehouse>
                               </House>
                              <?xml version="1.0" encoding="UTF-8"?>
                               <House>
                                  <Warehouse>
                                  <WarehouseId>7</WarehouseId>
                                  <WarehouseName>
                                     <Town>Southlake</Town>
                                     <State>Texas</State>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>25000</Area>
                                  <Docks>2</Docks>
                                  <DockType>Rear load</DockType>
                                  <WaterAccess>true</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Street</Parking>
                                  <VClearance>10</VClearance>
                                </Warehouse>
                                <Warehouse>
                                  <WarehouseId>8</WarehouseId>
                                  <WarehouseName>
                                     <Town>Bournemouth</Town>
                                     <State>Dorset</State>
                                  </WarehouseName>
                                  <WarehouseName>
                                     <Town>Shirley</Town>
                                     <County>West Midlands</County>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>30000</Area>
                                  <Docks>5</Docks>
                                  <DockType>Rear load</DockType>
                                  <WaterAccess>true</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Bay</Parking>
                                  <VClearance>10</VClearance>
                                </Warehouse>
                                <Warehouse>
                                  <WarehouseId>9</WarehouseId>
                                  <WarehouseName>
                                     <Town>Clapham</Town>
                                     <County>London</County>
                                  </WarehouseName>
                                  <Building>Owned</Building>
                                  <Area>10000</Area>
                                  <Docks>1</Docks>
                                  <DockType>Side load</DockType>
                                  <WaterAccess>false</WaterAccess>
                                  <RailAccess>N</RailAccess>
                                  <Parking>Bay</Parking>
                                  <VClearance>20</VClearance>
                                </Warehouse>
                               </House>
                              And the XFilehandler package looks like this (I'm just trying to do a simple select only on WarehouseId & WaterAccess for the time being to keep things simple):
                              create or replace package XFileHandler as
                               
                                TYPE TRECORD IS RECORD (
                                  WID     NUMBER(2)
                                , WACCESS VARCHAR2(5)
                                );
                               
                                type TRecordTable is table of TRecord;
                               
                                function getRows (p_directory in varchar2, p_filename in varchar2) return TRecordTable pipelined;
                               
                              end;
                              / 
                              
                              create or replace package body XFileHandler is
                               
                                function getRows (p_directory in varchar2, p_filename in varchar2)
                                 return TRecordTable pipelined
                                is
                               
                                  nb_rec          number := 1;
                                  tmp_xml        clob;
                                  tmp_file         clob;
                                  rec               TRecord;
                               
                                begin
                               
                                  DBMS_LOB.CREATETEMPORARY(TMP_FILE, TRUE);
                                  tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);
                               
                                  LOOP
                               
                                    tmp_xml := regexp_substr(tmp_file, '<\?xml[^?]+\?>\s*<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
                                    exit when length(tmp_xml) = 0;
                                    --dbms_output.put_line(tmp_rec);
                                    nb_rec := nb_rec + 1;
                               
                              
                                  select y.WID, y.WACCESS
                                  into rec.WID, rec.WACCESS
                                  from xmltable('/House' passing xmltype(tmp_xml)
                                                columns WID NUMBER(2) PATH 'Warehouse/WarehouseId',
                                                            WACCESS VARCHAR2(5) PATH 'WaterAccess') y;
                                                       
                                    pipe row ( rec );
                               
                                  end loop;
                               
                                  dbms_lob.freetemporary(tmp_file);
                                  
                                  return;
                               
                                end;
                               
                              end;
                              Now, when I run the query:
                              select * from table(XFileHandler.getRows('XML_DIR', 'XFileHandler_test.xml'));
                              I get the error: ORA-00600: internal error code, arguments: [17285], [0x5CFE8DC8], [4], [0x45ABE1C8], [], [], [], []

                              I had a look in the dump file for anything obvious, but nothing really stands out. Is there anything obvious in my code that I'm missing or something else which you may think could be causing this error, e.g in the regular expression regexp_substr?

                              Many thanks,
                              Shaun.
                              • 12. Re: Need to insert values into a table from a XML file
                                odie_63
                                Thanks for keeping the post open for me.
                                I'm not administrator nor moderator here, just volunteer :)
                                Unless there are abuses such as spam, double posting, thread hijacking (for the most common), threads are never really closed (locked).
                                The poster can then mark the thread as "Answered" if the original question has been answered, or the discussion came to an end.


                                I don't reproduce the internal error on 10.2.0.5, but there are some issues with the code nonetheless :

                                - XPath expressions are wrong : should be '/House/Warehouse' as the main XQuery expr, then 'WarehouseId' and 'WaterAccess' for the columns
                                - Each document contains multiple Warehouse records, therefore the SELECT INTO won't work. You have to add a nested loop to pipe each row.
                                - Don't forget to run the ALTER SESSION command (either from PL/SQL, or once in your session before running the query)

                                To sum up, try replacing this :
                                    select y.WID, y.WACCESS
                                    into rec.WID, rec.WACCESS
                                    from xmltable('/House' passing xmltype(tmp_xml)
                                                  columns WID NUMBER(2) PATH 'Warehouse/WarehouseId',
                                                              WACCESS VARCHAR2(5) PATH 'WaterAccess') y;
                                                         
                                      pipe row ( rec );
                                with :
                                for r in (
                                  select y.WID, y.WACCESS
                                  --into rec.WID, rec.WACCESS
                                  from xmltable('/House/Warehouse' passing xmltype(tmp_xml)
                                                columns WID     NUMBER(2)   PATH 'WarehouseId',
                                                        WACCESS VARCHAR2(5) PATH 'WaterAccess') y
                                ) 
                                loop
                                      
                                  rec.wid := r.wid;
                                  rec.waccess := r.waccess;
                                           
                                  pipe row ( rec );
                                  
                                end loop;
                                • 13. Re: Need to insert values into a table from a XML file
                                  964494
                                  Hi Odie,

                                  Thanks for the reply and examples. I could see that I was trying to insert multiple values into the one variable, so thanks for highlighting the use of nested loops to get round this.

                                  The trouble with the project I'm on is that the goalposts keep moving as soon as I'm close to completing my tasks. I've now been told that the production environment will be on v11.2.0.1. Thankfully, you originally posted solutions for 10g and 11g so I used the 11g bit for the XFileHandler package.

                                  Consequently, this is what I have at the moment:
                                  create or replace package XFileHandler as
                                   
                                    TYPE TRECORD IS RECORD (
                                      WID     NUMBER(2)
                                    , WACCESS VARCHAR2(5)
                                    );
                                   
                                    type TRecordTable is table of TRecord;
                                   
                                    function getRows (p_directory in varchar2, p_filename in varchar2) return TRecordTable pipelined;
                                   
                                  end;
                                  / 
                                  
                                  create or replace package body XFileHandler is
                                   
                                    function getRows (p_directory in varchar2, p_filename in varchar2)
                                     return TRecordTable pipelined
                                    is
                                   
                                      nb_rec          number := 1;
                                      tmp_xml         clob;
                                      tmp_file        clob;
                                      rec             TRecord;
                                   
                                    begin
                                   
                                      DBMS_LOB.CREATETEMPORARY(TMP_FILE, TRUE);
                                      tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);
                                   
                                      LOOP
                                   
                                        tmp_xml := regexp_substr(regexp_replace(tmp_file, '<!DOCTYPE  CMF_Doc SYSTEM "CMF_Doc.dtd">', '', 1, 1, 'n'), 
                                                                                '<\?xml[^?]+\?>\s*<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
                                        exit when length(tmp_xml) = 0;
                                        --dbms_output.put_line(tmp_rec);
                                        nb_rec := nb_rec + 1;
                                      
                                      for r in (
                                      select y.WID, y.WACCESS
                                      from xmltable('/House/Warehouse' passing xmltype(tmp_xml)
                                                  columns WID     NUMBER(2)   PATH 'WarehouseId',
                                                          WACCESS VARCHAR2(5) PATH 'WaterAccess') y
                                       ) 
                                      loop
                                            
                                        rec.wid := r.wid;
                                        rec.waccess := r.waccess;
                                                 
                                        pipe row ( rec );
                                        
                                      end loop;
                                  
                                                            
                                      dbms_lob.freetemporary(tmp_file);
                                      
                                      return;
                                   
                                    end loop;
                                   
                                   end;
                                  
                                  end;
                                  The XML file I'm using is the same one as my last post, so when I run the query:
                                  select * from table(XFileHandler.getRows('XML_DIR', 'XFileHandler_test.xml'));
                                  It is only returning the rows from the first XML message...
                                  WID  WACCESS
                                  ---  -------
                                  1    true
                                  2    true
                                  3    false
                                  Whereas, it should be looping until no more XML messages are left, thus displaying results like...
                                  WID  WACCESS
                                  ---  -------
                                  1    true
                                  2    true
                                  3    false
                                  4    true
                                  5    true
                                  6    false
                                  7    true
                                  8    true
                                  9    false
                                  It's obviously something to do with my regulare expressions, but I can't see anything in my limited knowledge so any advice is much appreciated.

                                  Thanks,
                                  Shaun.
                                  • 14. Re: Need to insert values into a table from a XML file
                                    odie_63
                                    It is only returning the rows from the first XML message...
                                    That's because you're exiting the function inside the loop, so nothing happens past the first iteration :
                                        ...
                                        end loop;
                                     
                                                              
                                        dbms_lob.freetemporary(tmp_file);
                                        
                                        return;
                                     
                                      end loop;
                                    Also, you don't need any regexp to remove the DTD declaration, a simple REPLACE() is sufficient, once, before starting the loop :
                                      function getRows (p_directory in varchar2, p_filename in varchar2)
                                        return TRecordTable pipelined
                                      is
                                     
                                        nb_rec          number := 1;
                                        tmp_xml         clob;
                                        tmp_file        clob;
                                        rec             TRecord;
                                     
                                      begin
                                     
                                        dbms_lob.createtemporary(tmp_file, true);
                                        tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename); 
                                        tmp_file := replace(tmp_file, '<!DOCTYPE  CMF_Doc SYSTEM "CMF_Doc.dtd">');
                                     
                                        loop
                                    
                                          tmp_xml := regexp_substr(tmp_file,'<\?xml[^?]+\?>\s*<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
                                          exit when length(tmp_xml) = 0;
                                          nb_rec := nb_rec + 1;
                                        
                                          for r in (
                                            select y.wid, y.waccess
                                            from xmltable('/House/Warehouse' passing xmltype(tmp_xml)
                                                        columns wid     number(2)   path 'WarehouseId',
                                                                waccess varchar2(5) path 'WaterAccess') y
                                          ) 
                                          loop
                                              
                                            rec.wid := r.wid;
                                            rec.waccess := r.waccess;
                                                   
                                            pipe row ( rec );
                                          
                                          end loop;
                                      
                                        end loop;
                                      
                                        dbms_lob.freetemporary(tmp_file);
                                        return;
                                     
                                      end;
                                    1 2 Previous Next