7 Replies Latest reply: Nov 15, 2013 1:51 AM by Imran Soudagar RSS

    xmltype : xml files data not getting loaded into table

    Imran Soudagar

      Hi,

      I have a requirement to load the where data will be received in the below XML format.

       

       

       

      <?xml version="1.0" encoding="UTF-8"?>
      <ns0:pHeader xmlns:ns0="http://hnw/Promise.wsdl">
        <ns1:creationDate xmlns:ns1="http://hnw/Promise.wsdl/types/">20131001</ns1:creationDate>
        <ns1:creationTime xmlns:ns1="http://hnw/Promise.wsdl/types/">031144</ns1:creationTime>
        <ns1:generationNumber xmlns:ns1="http://hnw/Promise.wsdl/types/"/>
        <ns1:lines xmlns:ns1="http://hnw/Promise.wsdl/types/">
          <ns1:array>
            <ns1:item>
              <ns1:customerRequestDate/>
              <ns1:description/>
              <ns1:destinationId>HPS-350</ns1:destinationId>
              <ns1:lineDependancy/>
              <ns1:lineNumber>0010</ns1:lineNumber>
              <ns1:material>TESTITEMX01</ns1:material>
              <ns1:orderReference>Test CN</ns1:orderReference>
              <ns1:promiseDate>131009</ns1:promiseDate>
              <ns1:promiseQuantity>1.000</ns1:promiseQuantity>
              <ns1:shipmentReference/>
              <ns1:shippedDate/>
              <ns1:shippedFlag/>
              <ns1:sourceId>HPS-350</ns1:sourceId>
              <ns1:status/>
              <ns1:supplierLineNumber/>
              <ns1:supplierOrderNumber/>
              <ns1:unitPrice/>
            </ns1:item>
          </ns1:array>
        </ns1:lines>
        <ns1:sourceId xmlns:ns1="http://hnw/Promise.wsdl/types/">HPS-350</ns1:sourceId>
      </ns0:pHeader>
      
      

       

      I followed below steps and the plsql block is getting completed successfully but the data is not getting loaded. I am not sure what could be the issue.

       

       

       

      SQL> 
      SQL> select * from v$version;
      BANNER                                                                          
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production    
      PL/SQL Release 11.2.0.3.0 - Production                                          
      CORE 11.2.0.3.0 Production                                                      
      TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production               
      NLSRTL Version 11.2.0.3.0 - Production                                         
      SQL> 
      SQL> CREATE TABLE XX_PROMISE_IN_ALL
        2  (
        3    SOURCE_ID              VARCHAR2(8),
        4    DESTINATION_ID         VARCHAR2(8),
        5    ORDER_REFERENCE        VARCHAR2(10),
        6    LINE_NUMBER            VARCHAR2(4),
        7    MATERIAL               VARCHAR2(26),
        8    DESCRIPTION            VARCHAR2(30),
        9    PROMISE_QUANTITY       VARCHAR2(6),
       10    PROMISE_DATE           VARCHAR2(6),
       11    SUPPLIER_ORDER_NUMBER  VARCHAR2(10),
       12    SUPPLIER_LINE_NUMBER   VARCHAR2(4),
       13    SHIPPED_FLAG           VARCHAR2(1),
       14    SHIPPED_DATE           VARCHAR2(6),
       15    STATUS                 VARCHAR2(1),
       16    UNIT_PRICE             VARCHAR2(12),
       17    CUSTOMER_REQUEST_DATE  VARCHAR2(6),
       18    SHIPMENT_REFERENCE     VARCHAR2(17)
       19    );
      Table created.
      SQL> create directory XX_PROMISE_IN_DIR as '/appsdata1/fis96/sapxi/uae786l/inbound';
      Directory created.
      SQL> ed
      Wrote file afiedt.buf
        1  DECLARE
        2   acct_doc xmltype := xmltype( bfilename('XX_PROMISE_IN_DIR','SAP_IN.xml'), nls_charset_id('AL32UTF8') );
        3  BEGIN
        4   insert into XX_PROMISE_IN_ALL (DESTINATION_ID, LINE_NUMBER, MATERIAL, ORDER_REFERENCE)
        5   select x.*
        6   from xmltable(
        7    'for $i in /pHeader
        8     return 
        9      for $j in $i/following-sibling::lines[1]/item
       10      return element r {$i, $j}'
       11    passing acct_doc
       12    columns DESTINATION_ID   number        path 'ITEM/destinationId',
       13            LINE_NUMBER      varchar2(100) path 'ITEM/lineNumber',
       14            MATERIAL         number        path 'ITEM/material',
       15            ORDER_REFERENCE  varchar2(100) path 'ITEM/orderReference'
       16   ) x
       17   ;
       18* END;
       19  /
      PL/SQL procedure successfully completed.
      SQL> 
      SQL> 
      SQL> 
      SQL> select * from XX_PROMISE_IN_ALL
        2  ;
      no rows selected
      SQL> spool off
      
      I am new to XML objects and could not analyze it in more detail. the tags in the above xml data are different that some examples I found on forums hence am confused if the tags I have written are correct.
       Any help is greatly appreciated.
       
       Thanks
       Imran
      
      
        • 1. Re: xmltype : xml files data not getting loaded into table
          Jason_(A_Non)

          I'll start with an example that shows the results then work backwards from there.

          SQL> select x.*
            2    from xmltable(XMLNamespaces('http://hnw/Promise.wsdl' AS "ns0",
            3                                'http://hnw/Promise.wsdl/types' AS "ns1"),
            4                  '/ns0:pHeader/ns1:lines/ns1:array/ns1:item'
            5      passing XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
            6   <ns0:pHeader xmlns:ns0="http://hnw/Promise.wsdl">
            7     <ns1:lines xmlns:ns1="http://hnw/Promise.wsdl/types">
            8       <ns1:array>
            9         <ns1:item>
           10           <ns1:destinationId>HPS-350</ns1:destinationId>
           11           <ns1:lineNumber>0010</ns1:lineNumber>
           12           <ns1:material>TESTITEMX01</ns1:material>
           13           <ns1:orderReference>Test CN</ns1:orderReference>
           14         </ns1:item>
           15       </ns1:array>
           16     </ns1:lines>
           17   </ns0:pHeader>')
           18      columns DESTINATION_ID   varchar2(10) path 'ns1:destinationId',
           19              LINE_NUMBER      varchar2(10) path 'ns1:lineNumber',
           20              MATERIAL         varchar2(15) path 'ns1:material',
           21              ORDER_REFERENCE  varchar2(10) path 'ns1:orderReference'
           22         ) x;
          
          DESTINATION_ID LINE_NUMBER MATERIAL        ORDER_REFERENCE
          -------------- ----------- --------------- ---------------
          HPS-350        0010        TESTITEMX01     Test CN
          
          

          You encountered the most common issue when parsing XML.  Namespaces.

           

          How I got from what you posted to the above.

          First I took your XML and stripped out all the unnecessary nodes, simply to make it smaller.

          I then replaced your

          passing acct_doc

          with

          passing XMLTYPE('<your XML here>')

          and ran the query.  As you would see, that returns no rows, hence the reason nothing was inserted and no errors raised.  Looking at what you were intending to extract from the XML, I then rewrote it, taking the simple approach with a simple XPath using XMLTable as it could duplicate what you were trying to do in XQuery.  You will need to change the passing clause so it again reads

          passing acct_doc

           

          Side note for older versions: The above query I show runs in 11.1.0.6, but returns blank rows.  Appears to be a bug in handling the similar namespaces but I did not look it it further so do not know when between 11.1.0.6 and 11.2.0.3 it was fixed.

          • 2. Re: xmltype : xml files data not getting loaded into table
            odie_63

            I am new to XML objects and could not analyze it in more detail.

            You know, all this is documented, you don't have to try things at random.

            I encourage you to start reading this and more generally the XML DB Developer Guide from the official documentation :

            Using XQuery with Oracle XML DB


            Here's a simple example to help you understand how it works.

            Say you have to load this XML document :

            <root>

              <item>

                <col1>1</col1>

              </item>

              <item>

                <col1>2</col1>

                <col2>ABC</col2>

              </item>

            </root>

            into this table :

            create table sample_data (

              col1 number

            , col2 varchar2(30)

            );

             

            You can do it easily using XMLTable :

            select x.col1, x.col2

            from xmltable(

                   '/root/item'

                   passing xml_data

                   columns col1 number       path 'col1'

                         , col2 varchar2(30) path 'col2'

                 ) x

            ;

            where :

            '/root/item' is the XQuery expression (here in the form of a plain XPath expression) defining the "row" pattern.

            In the present case, when the expression is evaluated, it will produce a sequence of two <item> nodes.

             

            xml_data is the input XML document, in the form of an XMLType variable or column.

             

            The last part is the COLUMNS clause (in blue). This part defines the different column patterns we need to split each <item> node into different relational columns.

            Here, we instruct the function to create two resulting columns out of each item : col1 and col2, respectively based on XML elements <col1> and <col2>, finally returning the following result set :

             

                  COL1 COL2

            ---------- ------------------------------

                     1

                     2 ABC

             

            Now, your real document is little more complex but not that much. In particular, see Jason's post to know how to throw namespaces into the game.

            If you've understood the concepts, you'll be able to handle most common cases.

            • 3. Re: xmltype : xml files data not getting loaded into table
              Imran Soudagar


              Thanks Jason and Odie for the explanations. I am still in the process of understanding the basics of XML DB so have a lot of queries.

               

              I still could not understand below points:

              1. What are namespaces and how/where to add these in the "select" syntax? do you have an example that I can refer to?

              2. In case I want to insert data from pHeader block in the table along with the Items block, I checked Odie's reply on the forum and you have used a for loops...

              <code> https://forums.oracle.com/thread/2182669<code>

              3. When we assign the path to the columns, in the above link, Odie has used the "Header/element name" or "Detail/element name". but in above reply from Jason, he has only mentioned the element names plus Jason has not used the "for" loops.

               

              Thanks Odie for the XML DB Dev Guide's link. I will surely take a look at it.

               

              Regards

              Imran

              • 4. Re: xmltype : xml files data not getting loaded into table
                Jason_(A_Non)

                1) Here is one answer from a search on XML Namespaces.  XML namespace - Wikipedia, the free encyclopedia. An example would be a Person.  I define my schema with a Person element with five child nodes.  You define a schema with a Person element with eight child nodes.  A third party comes along and wants to use my Person element definition and also allow the use of your definition in the same XML file.  Without a namespace, there is no way to distinguish whether the Person element is the one I defined or the one you defined.  Validation and parses do not know what to expect.  The namespace allows both our definitions to exist in the same XML file, yet be identifiable as to which definition they follow.

                 

                2 & 3) Your example showed only using information from the item group so both our examples grabbed from that node only.  The example you linked to has a parent/child or master/detail relationship between the data and that is why a slightly different approach is needed.  Are you trying to load all this data into a single row in a table or are you trying to create a parent/child table relationship.

                • 5. Re: xmltype : xml files data not getting loaded into table
                  Imran Soudagar

                  Thanks Jason for explanation in point 1,

                   

                  Regarding points 2 and 3, my requirement is only to insert data from item group (child group) into the table. But I was going through the link provided above where there is parent / child relation in the xml and for clause is used, I could not understand how the for loop is working in that case.

                  I tried to insert parent and child data in a single table (creation date from header block need to be entered in shipped_date column along with other child values) but am getting error:

                  I have difficulty understanding lines 8 till 12.

                   

                  SQL> 
                  SQL> ed
                  Wrote file afiedt.buf
                    1  DECLARE
                    2   acct_doc xmltype := xmltype( bfilename('XX_PROMISE_IN_DIR','SAP_IN.xml'), nls_charset_id('AL32UTF8') );
                    3  BEGIN
                    4   insert into XX_PROMISE_IN_ALL (SHIPPED_DATE, DESTINATION_ID, LINE_NUMBER, MATERIAL, ORDER_REFERENCE)
                    5   select x.*
                    6   from xmltable(
                    7    XMLNamespaces('http://hnw/Promise.wsdl' AS "ns0",'http://hnw/Promise.wsdl/types' AS "ns1"),
                    8   '/ns0:pHeader/ns1:lines/ns1:array/ns1:item'
                    9    'for $i in /pHeader
                   10     return 
                   11      for $j in $i/following-sibling::lines[1]/item
                   12      return element r {$i, $j}'
                   13    passing acct_doc
                   14    columns 
                   15  SHIPPED_DATE     DATE         PATH 'ns1:creationDate',
                   16  DESTINATION_ID   varchar2(10) path 'ns1:destinationId',
                   17  LINE_NUMBER      varchar2(10) path 'ns1:lineNumber',
                   18  MATERIAL         varchar2(15) path 'ns1:material',
                   19  ORDER_REFERENCE  varchar2(10) path 'ns1:orderReference'
                   20   ) x;
                   21* END;
                   22  /
                    'for $i in /pHeader
                    *
                  ERROR at line 9:
                  ORA-06550: line 9, column 3: 
                  PL/SQL: ORA-02000: missing COLUMNS keyword 
                  ORA-06550: line 4, column 2: 
                  PL/SQL: SQL Statement ignored 
                  
                  SQL> spool off
                  
                  

                   

                  Regards

                  Imran

                  • 6. Re: xmltype : xml files data not getting loaded into table
                    odie_63

                    But I was going through the link provided above where there is parent / child relation in the xml and for clause is used, I could not understand how the for loop is working in that case.

                    Don't focus on this other thread, the XQuery I used there was meant to handle the "non-conventional" structure of the XML (no container to materialize repeating subgroups).

                     

                    In your case, you have a classic parent-child relationship with one parent (pHeader) and multiple children (item).

                    Maybe, you'll find the approach easier to understand with two XMLTABLEs :

                     

                    select h.SHIPPED_DATE

                         , d.*

                    from xmltable(

                           xmlnamespaces(

                             'http://hnw/Promise.wsdl' as "ns0"

                           , 'http://hnw/Promise.wsdl/types/' as "ns1"

                           )

                         , '/ns0:pHeader'  

                           passing acct_doc

                           columns SHIPPED_DATE  date    path 'ns1:creationDate'

                                 , ITEMS         xmltype path 'ns1:lines/ns1:array/ns1:item'

                         ) h

                       , xmltable(

                           xmlnamespaces(default 'http://hnw/Promise.wsdl/types/')

                         , '/item'  

                           passing h.items

                           columns DESTINATION_ID   varchar2(10) path 'destinationId'

                                 , LINE_NUMBER      varchar2(10) path 'lineNumber'

                                 , MATERIAL         varchar2(15) path 'material'

                                 , ORDER_REFERENCE  varchar2(10) path 'orderReference'

                         ) d

                    ;

                     

                    The first XMLTABLE, aliased "h" (for header), extracts two pieces of data :

                    - the creationDate

                    - the sequence of items

                     

                    The second XMLTABLE ("d" for details), takes the sequence of items passed from the first XMLTABLE (see the red parts) and break it into separate rows (that's what the expression '/item' does) and further into separate columns (that's what the COLUMNS clause does).

                    Note that for the 2nd XMLTABLE, we only need to declare the 'http://hnw/Promise.wsdl/types/' namespace uri, since all the nodes we're interested in belong to this namespace only. And I've used the DEFAULT declaration syntax so that we don't even need to prefix all names.

                    • 7. Re: xmltype : xml files data not getting loaded into table
                      Imran Soudagar


                      Hats off to you Odie!!!

                      That was a really simple explanation and your breaking the data into two xmltables helped me understand the logic. This opens a new door for me in file handling processes other than using utl_file and sqlloaders. I will go thorugh the XML DB guide for more details into this topic.

                       

                      I am still facing an issue that I cannot figure out.

                      I copied your select script into the PLSQL block and it works fine if I pass the xmltype directly to the xmltable without storing it into the parameter acct_doc.

                      but if I store it in the acct_doc parameter and then pass to the query, the row is not getting inserted.

                       

                      Please refer below scripts:

                       

                       

                      SQL> 
                      SQL> ed
                      Wrote file afiedt.buf
                        1  DECLARE
                        2   acct_doc xmltype := xmltype( bfilename('XX_PROMISE_IN_DIR','SAP_IN_ACK1232.xml'), nls_charset_id('AL32UTF8') );
                        3  BEGIN
                        4  insert into XX_PROMISE_IN_ALL (SHIPPED_DATE, DESTINATION_ID, LINE_NUMBER, MATERIAL, ORDER_REFERENCE)
                        5  select to_char(h.SHIPPED_DATE, 'RRMMDD')
                        6       , d.*
                        7  from xmltable(
                        8         xmlnamespaces(
                        9           'http://hnw/Promise.wsdl' as "ns0"
                       10         , 'http://hnw/Promise.wsdl/types/' as "ns1"
                       11         )
                       12       , '/ns0:pHeader'   
                       13         passing acct_doc
                       14         columns SHIPPED_DATE  date    path 'ns1:creationDate'
                       15               , ITEMS         xmltype path 'ns1:lines/ns1:array/ns1:item'
                       16       ) h
                       17     , xmltable(
                       18         xmlnamespaces(default 'http://hnw/Promise.wsdl/types/')
                       19       , '/item'   
                       20         passing h.items
                       21         columns DESTINATION_ID   varchar2(10) path 'destinationId'
                       22               , LINE_NUMBER      varchar2(10) path 'lineNumber'
                       23               , MATERIAL         varchar2(15) path 'material'
                       24               , ORDER_REFERENCE  varchar2(10) path 'orderReference' 
                       25       ) d
                       26  ;
                       27  dbms_output.put_line(SQL%rowcount||' row inserted.');
                       28  commit;
                       29* END;
                       30  /
                      0 row inserted.                                                                 
                      PL/SQL procedure successfully completed.
                      SQL> ed
                      Wrote file afiedt.buf
                        1  DECLARE
                        2   acct_doc xmltype := xmltype( bfilename('XX_PROMISE_IN_DIR','SAP_IN_ACK1232.xml'), nls_charset_id('AL32UTF8') );
                        3  BEGIN
                        4  insert into XX_PROMISE_IN_ALL (SHIPPED_DATE, DESTINATION_ID, LINE_NUMBER, MATERIAL, ORDER_REFERENCE)
                        5  select to_char(h.SHIPPED_DATE, 'RRMMDD')
                        6       , d.*
                        7  from xmltable(
                        8         xmlnamespaces(
                        9           'http://hnw/Promise.wsdl' as "ns0"
                       10         , 'http://hnw/Promise.wsdl/types/' as "ns1"
                       11         )
                       12       , '/ns0:pHeader'   
                       13         passing xmltype( bfilename('XX_PROMISE_IN_DIR','SAP_IN_ACK1232.xml'), nls_charset_id('AL32UTF8') )
                       14         columns SHIPPED_DATE  date    path 'ns1:creationDate'
                       15               , ITEMS         xmltype path 'ns1:lines/ns1:array/ns1:item'
                       16       ) h
                       17     , xmltable(
                       18         xmlnamespaces(default 'http://hnw/Promise.wsdl/types/')
                       19       , '/item'   
                       20         passing h.items
                       21         columns DESTINATION_ID   varchar2(10) path 'destinationId'
                       22               , LINE_NUMBER      varchar2(10) path 'lineNumber'
                       23               , MATERIAL         varchar2(15) path 'material'
                       24               , ORDER_REFERENCE  varchar2(10) path 'orderReference' 
                       25       ) d
                       26  ;
                       27  dbms_output.put_line(SQL%rowcount||' row inserted.');
                       28  commit;
                       29* END;
                      SQL> /
                      1 row inserted.                                                                 
                      PL/SQL procedure successfully completed.
                      SQL> spool off
                      
                      

                       

                      The change in the above scripts are at line number 13 but that in my opinion should not cause such issue.

                      Once again thanks for your help.

                       

                      Regards

                      Imran