This discussion is archived
9 Replies Latest reply: Mar 7, 2012 8:39 AM by user1107506 RSS

How to extract data using PL/SQL

user1107506 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you for all the help.

    Ankith

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points