5 Replies Latest reply: Dec 20, 2012 10:03 AM by 937454 RSS

    XML feed insertion to a table

    937454
      Hi,
      My db version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

      I have an xml like below:
      <?xml version="1.0"?>
      <ROWSET>
          <ROW>
          <id>123</id>
          <aname>1</aname>
          <a>CNET</a>
          <b>21416911</b>
          <c>12345678</c>
          <d>878787</d>
          </ROW>
          <ROW>
          <id>234</id>
          <aname>2</aname>    
          <a>DNET</a>
          <b>21416911</b>
          <c>12345678</c>
          <d>878787</d>
          </ROW>    
      </ROWSET>
       
      creating a test table:
      CREATE TABLE test (
          id NUMBER(16),
          aname VARCHAR2(30),
          friendlyname VARCHAR2(150),
          value varchar2(200));
      I want to insert the xml into the table test as below
      SELECT * FROM test;
      id     aname     friendlyname     value
      123     1     a     CNET
      123     1     b     21416911
      123     1     c     12345678
      123     1     d     878787
      234     2     a     DNET
      234     2     b     21416911
      234     2     c     12345678
      234     2     d     878787
      The elements <id> and <aname> are mandatory. They come with every xml feed.
      Please let me know if it is possible.

      Edited by: 934451 on Dec 14, 2012 12:00 PM

      Edited by: 934451 on Dec 14, 2012 12:15 PM
        • 1. Re: XML feed insertion to a table
          odie_63
          Welcome back.

          You can do it like this, assuming all elements besides <id> and <aname> always come after <aname> :
          SQL> DECLARE
            2  
            3    xmldoc xmltype := xmltype(
            4  '<?xml version="1.0"?>
            5  <ROWSET>
            6      <ROW>
            7      <id>123</id>
            8      <aname>1</aname>
            9      <a>CNET</a>
           10      <b>21416911</b>
           11      <c>12345678</c>
           12      <d>878787</d>
           13      </ROW>
           14      <ROW>
           15      <id>234</id>
           16      <aname>2</aname>
           17      <a>DNET</a>
           18      <b>21416911</b>
           19      <c>12345678</c>
           20      <d>878787</d>
           21      </ROW>
           22  </ROWSET>') ;
           23  
           24  BEGIN
           25  
           26    INSERT INTO test (id, aname, friendlyname, value)
           27    SELECT x1.id, x1.aname, x2.fname, x2.value
           28    FROM XMLTable(
           29           '/ROWSET/ROW'
           30           passing xmldoc
           31           columns id       number(16)    path 'id'
           32                 , aname    varchar2(30)  path 'aname'
           33                 , xml_coll xmltype       path 'aname/following-sibling::*'
           34         ) x1
           35       , XMLTable(
           36           '*'
           37           passing x1.xml_coll
           38           columns fname varchar2(150) path 'local-name(.)'
           39                 , value varchar2(200) path 'text()'
           40         ) x2 ;
           41  
           42  END;
           43  /
           
          PL/SQL procedure successfully completed
           
          SQL> select * from test;
           
                         ID ANAME                          FRIENDLYNAME    VALUE
          ----------------- ------------------------------ --------------- --------------------
                        123 1                              a               CNET
                        123 1                              b               21416911
                        123 1                              c               12345678
                        123 1                              d               878787
                        234 2                              a               DNET
                        234 2                              b               21416911
                        234 2                              c               12345678
                        234 2                              d               878787
           
          8 rows selected
           
          The following-sibling axis may show bad performance on large XMLs.
          It would make more sense and be easier to process if you had a, b, c and d wrapped into their own enclosing element :
          <ROWSET>
            <ROW>
              <id>123</id>
              <aname>1</aname>
              <ITEMS>
                <a>CNET</a>
                <b>21416911</b>
                <c>12345678</c>
                <d>878787</d>
              <ITEMS>
            </ROW>
            ...
          • 2. Re: XML feed insertion to a table
            Shra1
            Odie , as always - Great Tips - I'm raising a No Data found when I tried my following PLSQL Block...Can you please provide some tips on how to get the values of All the Ack, CorrleationId, TimeStamp Version from this following example? We're running on 11gR1. Thanks
            ----------
            DECLARE
            l_xmldoc XMLType := XMLType(
            '<?xml version="1.0" encoding="utf-8"?>
            <ExecuteInterfaceResponse xmlns="http://tempuri.org/">
            <ExecuteInterfaceResult>
            <UnderwriterProfileResponse xmlns="">
            <ResponseHeader>
            <Ack>SUCCESS</Ack>
            <CorrelationId>36726</CorrelationId>
            <Timestamp>2012-12-17T14:40:50</Timestamp>
            <Version>1.0.0</Version>
            </ResponseHeader>
            </UnderwriterProfileResponse>
            </ExecuteInterfaceResult>
            </ExecuteInterfaceResponse>
            ');
            v_ack VARCHAR2(30);
            v_rId PLS_INTEGER;
            BEGIN
            --INSERT INTO xml_read_test(ack, xr_id)
            SELECT
            --x2.ack, x2.RId
            x1.Ackvalue, x1.RecommId
            INTO v_ack, v_rId
            FROM XMLTable(
            '/ExecuteInterfaceResponse/ExecuteInterfaceResult/UnderwriterProfileResponse/ResponseHeader'
            passing l_xmldoc
            columns
            AckValue Varchar2(30) path 'Ack'
            , RecommId NUMBER(30) path 'CorrelationId'
            , xml_coll xmltype path 'ResponseHeader/following-sibling::*'
            ) x1
            , XMLTable(
            '*'
            passing x1.xml_coll
            columns Ack varchar2(150) path 'Ack'
            , RId varchar2(200) path 'CorrelationId'
            ) x2
            ;
            dbms_output.put_line('Ack: '||v_ack||' RId: '||v_rId);
            END;
            /
            • 3. Re: XML feed insertion to a table
              odie_63
              Shra1 wrote:
              Odie , as always - Great Tips - I'm raising a No Data found when I tried my following PLSQL Block...Can you please provide some tips on how to get the values of All the Ack, CorrleationId, TimeStamp Version from this following example? We're running on 11gR1. Thanks
              Well, first you have to understand why I used the following-sibling axis in the first example.
              OP wanted to extract a kind of master-detail relationship, but out of sibling elements, which is not something we usually do, hence this trick using following-sibling.

              As far as I can see, that's not your scenario.
              In your case, simply use a single XMLTable, and do no forget namespaces ;)
              SQL> DECLARE
                2  l_xmldoc XMLType := XMLType(
                3  '<?xml version="1.0" encoding="utf-8"?>
                4  <ExecuteInterfaceResponse xmlns="http://tempuri.org/">
                5  <ExecuteInterfaceResult>
                6  <UnderwriterProfileResponse xmlns="">
                7  <ResponseHeader>
                8  <Ack>SUCCESS</Ack>
                9  <CorrelationId>36726</CorrelationId>
               10  <Timestamp>2012-12-17T14:40:50</Timestamp>
               11  <Version>1.0.0</Version>
               12  </ResponseHeader>
               13  </UnderwriterProfileResponse>
               14  </ExecuteInterfaceResult>
               15  </ExecuteInterfaceResponse>
               16  ');
               17  v_ack VARCHAR2(30);
               18  v_rId PLS_INTEGER;
               19  v_ts  TIMESTAMP;
               20  BEGIN
               21  --INSERT INTO xml_read_test(ack, xr_id)
               22  SELECT x1.Ackvalue, x1.RecommId, x1.tstamp
               23  INTO v_ack, v_rId, v_ts
               24  FROM XMLTable(
               25        XMLNamespaces('http://tempuri.org/' as "ns0"),
               26        '/ns0:ExecuteInterfaceResponse/ns0:ExecuteInterfaceResult/UnderwriterProfileResponse/ResponseHeader'
               27        passing l_xmldoc
               28        columns
               29          AckValue Varchar2(30) path 'Ack'
               30        , RecommId NUMBER(30) path 'CorrelationId'
               31        , tstamp   timestamp  path 'Timestamp'
               32       ) x1 ;
               33  dbms_output.put_line('Ack: '||v_ack||' RId: '||v_rId||' Ts: '||v_ts);
               34  END;
               35  /
               
              Ack: SUCCESS RId: 36726 Ts: 17/12/12 14:40:50,000000
               
              PL/SQL procedure successfully completed
               
              • 4. Re: XML feed insertion to a table
                Shra1
                Simply Awesome..Great Suggestions! It worked! :)
                • 5. Re: XML feed insertion to a table
                  937454
                  Thank you Odie , it works.
                  Sorry, I could'nt back to this thread earlier.