9 Replies Latest reply: Mar 7, 2012 4:39 PM by user1107506 RSS

    How to extract data using PL/SQL

    user1107506
      Hi
      I am new to XMl. I need to extract id, type, number-out from the below given xml data. Any help will be highly appreciated.

      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:i24n="http://www.w3.org/2005/09/ws-i24n" xmlns:typ="http://oracle.com/determinations/server/10.3/rulebase/assess/types">
      <SOAP-ENV:Envelope>
      <SOAP-ENV:Header>
      <i24n:international>
      <i24n:locale>en_US</i24n:locale>
      <i24n:tz>GMT-0700</i24n:tz>
      </i24n:international>
      </SOAP-ENV:Header>
      <SOAP-ENV:Body>
      <typ:assess-response>
      <typ:global-instance>
      <typ:attribute id="myfee" type="currency">
      <typ:number-out>6.0</typ:number-out>
      </typ:attribute>
      <typ:entity id="entity_fee">
      <typ:instance id="8899776">
      <typ:attribute id="mycost" type="currency" inferred="true">
      <typ:number-out>108.0</typ:number-out>
      </typ:attribute>
      <typ:attribute id="myplace" type="text">
      <typ:text-out>K</typ:text-out>
      </typ:attribute>
      <typ:attribute id="myroll" type="text">
      <typ:text-out>CCJ</typ:text-out>
      </typ:attribute>
      </typ:instance>
      </typ:entity>
      </typ:global-instance>
      </typ:assess-response>
      </SOAP-ENV:Body>
      </SOAP-ENV:Envelope>

      Thanks

      Ankith
        • 1. Re: How to extract data using PL/SQL
          odie_63
          Hi,

          Use the search feature, there's already a ton of examples in the forum.

          If you still have issues :
          - give your database version (select * from v$version)
          - where does the XML resides? File, variable, column ? Which datatype?
          - show your best attempt so far
          - explain in what form you require the extracted data : individual variables, resultset, ... ?
          • 2. Re: How to extract data using PL/SQL
            Jason_(A_Non)
            For an additional hint, if your version is less than 10.2, you will find examples with extract and extractValue. If 10.2 or later, you will find XMLTable and maybe even XQuery examples.
            • 3. Re: How to extract data using PL/SQL
              user1107506
              Hi,

              Database 11g. version 11.2.0.2.0. XML is stored in xmltype column in table. I need to extract individaul values from this and insert into table.

              for eg : id="myfee"
              type="currency">
              number-out=6.0
              Any help will be appreciated.

              Thanks
              Ankith
              • 4. Re: How to extract data using PL/SQL
                Jason_(A_Non)
                To repeat
                Use the search feature, there's already a ton of examples in the forum.
                Look for XMLTable. You'll need XMLNamespaces as part of the XMLTable setup.

                Show us a first cut at something. Here's one template you'll find when searching for examples here an in the {forum:id=34} forum.
                SELECT alias2.*
                  FROM table alias1,
                       XMLTable(XMLNamespaces(),
                                ''
                                PASSING alias1.column
                                COLUMNS
                               ) alias2;
                • 5. Re: How to extract data using PL/SQL
                  user1107506
                  Hi

                  Not sure what i am doing wrong. the result is null. Your help to solve this will be much appreciated.

                  with t as(
                  select xmltype(
                  '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                  xmlns:i24n="http://www.w3.org/2005/09/ws-i24n"
                  xmlns:typ="http://oracle.com/determinations/server/10.3/rulebase/assess/types">
                  <SOAP-ENV:Header>
                  <i24n:international>
                  <i24n:locale>en_US</i24n:locale>
                  <i24n:tz>GMT-0700</i24n:tz>
                  </i24n:international>
                  </SOAP-ENV:Header>
                  <SOAP-ENV:Body>
                  <typ:assess-response>
                  <typ:global-instance>
                  <typ:attribute id="Currency_type" type="dollar">
                  <typ:number-val>100.0</typ:number-val>
                  </typ:attribute>
                  <typ:entity id="entity_currency">
                  <typ:instance id="cur_8899_D">
                  <typ:attribute id="currency_fee" type="dollar" inferred="true">
                  <typ:number-val>16.0</typ:number-val>
                  </typ:attribute>
                  <typ:attribute id="currency_v_type" type="USD">
                  <typ:text-val>D</typ:text-val>
                  </typ:attribute>
                  </typ:instance>
                  </typ:entity>
                  </typ:global-instance>
                  </typ:assess-response>
                  </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>') col
                  from dual)
                  select x.*
                  from t,
                  xmltable('/assess-response/global-instance/attribute'
                  passing t.col
                  columns id_1 varchar2(100) path '@id',
                  type_1 varchar2(100) path '@type',
                  val varchar2(100) path '/attribute'

                  ) x
                  where id_1 ='Currency_type'
                  or id_1 ='currency_fee' ;

                  thanks in advance
                  Ankith
                  • 6. Re: How to extract data using PL/SQL
                    Jason_(A_Non)
                    Part of your issue was namespaces, the bane of XM. That is why my XMLTable template included an XMLNamespaces entry. Another part of your issue was an incorrect XPath. You forgot to account for the SOAP envelope the message was in. Taking those into account some working SQL would look like
                    with t as(
                    select xmltype(
                    '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                    xmlns:i24n="http://www.w3.org/2005/09/ws-i24n"
                    xmlns:typ="http://oracle.com/determinations/server/10.3/rulebase/assess/types">
                    <SOAP-ENV:Header>
                    <i24n:international>
                    <i24n:locale>en_US</i24n:locale>
                    <i24n:tz>GMT-0700</i24n:tz>
                    </i24n:international>
                    </SOAP-ENV:Header>
                    <SOAP-ENV:Body>
                    <typ:assess-response>
                    <typ:global-instance>
                    <typ:attribute id="Currency_type" type="dollar">
                    <typ:number-val>100.0</typ:number-val>
                    </typ:attribute>
                    <typ:entity id="entity_currency">
                    <typ:instance id="cur_8899_D">
                    <typ:attribute id="currency_fee" type="dollar" inferred="true">
                    <typ:number-val>16.0</typ:number-val>
                    </typ:attribute>
                    <typ:attribute id="currency_v_type" type="USD">
                    <typ:text-val>D</typ:text-val>
                    </typ:attribute>
                    </typ:instance>
                    </typ:entity>
                    </typ:global-instance>
                    </typ:assess-response>
                    </SOAP-ENV:Body>
                    </SOAP-ENV:Envelope>') col
                    from dual)
                    select x.*
                      from t,
                           xmltable(XMLNamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV",
                                                  'http://oracle.com/determinations/server/10.3/rulebase/assess/types' AS "typ"),
                                    'SOAP-ENV:Envelope/SOAP-ENV:Body/typ:assess-response/typ:global-instance/typ:attribute'
                                    --'SOAP-ENV:Envelope/SOAP-ENV:Body/typ:assess-response/typ:global-instance/typ:attribute[@id="Currency_type" or @id="currency_fee"]'
                                    passing t.col
                                    columns 
                                    id_1 varchar2(100) path '@id',
                                    type_1 varchar2(100) path '@type',
                                    val varchar2(100) path 'typ:number-val'
                                   ) x
                     where id_1 ='Currency_type'
                        or id_1 ='currency_fee';
                    Where possible, try to use full XPaths, due to performance reasons. I included a second XPath to show you could move your WHERE clause filters into the XPath itself. I also was not sure what you were trying to do with currency_fee since that sits at a different XPath location than currency_type.

                    As you are on 11.2, XQuery is another option but don't worry about it for now until you get the basics via XMLTable going first.

                    See the FAQ for how to use the code tag to wrap your text and retain formatting as shown above.
                    • 7. Re: How to extract data using PL/SQL
                      user1107506
                      I tried executing this script. It gave me an error

                      Error report:
                      SQL Error: ORA-31011: XML parsing failed
                      ORA-19202: Error occurred in XML processing
                      LPX-00601: Invalid token in: '/*/typ:number-val'
                      31011. 00000 - "XML parsing failed"
                      *Cause:    XML parser returned an error while trying to parse the document.
                      *Action:   Check if the document to be parsed is valid.

                      --
                      when i remark --val     varchar2(100) path 'typ:number-val' and execute it returns only one row.
                      Also i need to get the currency_fee.
                      could you pls help me solving this.

                      Thanks in advance.
                      • 8. Re: How to extract data using PL/SQL
                        odie_63
                        Works for me.

                        If you want to extract attributes at different levels, use a descendant axis :
                        SQL> with t as(
                          2  select xmltype(
                          3  '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                          4  xmlns:i24n="http://www.w3.org/2005/09/ws-i24n"
                          5  xmlns:typ="http://oracle.com/determinations/server/10.3/rulebase/assess/types">
                          6  <SOAP-ENV:Header>
                          7  <i24n:international>
                          8  <i24n:locale>en_US</i24n:locale>
                          9  <i24n:tz>GMT-0700</i24n:tz>
                         10  </i24n:international>
                         11  </SOAP-ENV:Header>
                         12  <SOAP-ENV:Body>
                         13  <typ:assess-response>
                         14  <typ:global-instance>
                         15  <typ:attribute id="Currency_type" type="dollar">
                         16  <typ:number-val>100.0</typ:number-val>
                         17  </typ:attribute>
                         18  <typ:entity id="entity_currency">
                         19  <typ:instance id="cur_8899_D">
                         20  <typ:attribute id="currency_fee" type="dollar" inferred="true">
                         21  <typ:number-val>16.0</typ:number-val>
                         22  </typ:attribute>
                         23  <typ:attribute id="currency_v_type" type="USD">
                         24  <typ:text-val>D</typ:text-val>
                         25  </typ:attribute>
                         26  </typ:instance>
                         27  </typ:entity>
                         28  </typ:global-instance>
                         29  </typ:assess-response>
                         30  </SOAP-ENV:Body>
                         31  </SOAP-ENV:Envelope>') col
                         32  from dual)
                         33  select x.*
                         34  from t,
                         35       xmltable(XMLNamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV",
                         36                                'http://oracle.com/determinations/server/10.3/rulebase/assess/types' AS "typ")
                         37       , 'SOAP-ENV:Envelope/SOAP-ENV:Body/typ:assess-response/typ:global-instance/descendant::typ:attribute'
                         38         passing t.col
                         39         columns
                         40           id_1 varchar2(100) path '@id',
                         41           type_1 varchar2(100) path '@type',
                         42           val varchar2(100) path 'typ:number-val'
                         43       ) x
                         44  ;
                         
                        
                        ID_1                     TYPE_1        VAL
                        ------------------------ ------------- ----------
                        Currency_type            dollar        100.0
                        currency_fee             dollar        16.0
                        currency_v_type          USD           
                         
                        • 9. Re: How to extract data using PL/SQL
                          user1107506
                          Thank you for all the help.

                          Ankith