This discussion is archived
12 Replies Latest reply: Oct 2, 2012 10:05 AM by Jason_(A_Non) RSS

Read XML data without name spaces from PL/SQL

794743 Newbie
Currently Being Moderated
Hi,

I am trying to upload XML data without namespaces into Oracle tables from PL/SQL. Below is the sample XML data.
Please let me know,How this is achievable in PL/SQL?

<?xml version="1.0" ?>
<insplist ver="2" count="1">
<insp id="219991" timestamp="134817078" stat="G" operator_id="999999" >
<data name="TIME CLOCK - PUNCH" val="INNER" type="STRING" />
</insp>
</insplist>

Thanks
  • 1. Re: Read XML data without name spaces from PL/SQL
    odie_63 Guru
    Currently Being Moderated
    Hi,
    I am trying to upload XML data without namespaces into Oracle tables from PL/SQL.
    You'll have to be a lot more specific than that.
    What does "upload" mean?

    - Do you want to insert the whole XML into a single CLOB/XMLType column?
    - Do you want to store element and attribute values into different columns?

    - What's the database version?
    - Where does the XML reside? PL/SQL variable (or input parameter), which datatype? External file?

    There are tons of examples you can find on this forum. Just search for terms like "Load XML into Oracle" etc.

    If you need specific help, please explain the mapping you want between the XML values and the target column(s).
  • 2. Re: Read XML data without name spaces from PL/SQL
    794743 Newbie
    Currently Being Moderated
    Hi ,

    Thanks.Let me give me some information.

    - Do you want to insert the whole XML into a single CLOB/XMLType column?
    No.I will extract the values and load into normal table columns.

    - Do you want to store element and attribute values into different columns?
    I would like to store only attribute value into columns.

    - What's the database version?
    The version is 11gR2.

    - Where does the XML reside? PL/SQL variable (or input parameter), which datatype? External file?
    I will receive XML data by UTL_HTTP.Response. Basically,I prepare an url and post it to thru UTL_HTTP and in response,I receive XML data.
    The program what I am going to use is Oracle PLSQL.

    Please let me know for any further udpates.
  • 3. Re: Read XML data without name spaces from PL/SQL
    odie_63 Guru
    Currently Being Moderated
    I will receive XML data by UTL_HTTP.Response. Basically,I prepare an url and post it to thru UTL_HTTP and in response,I receive XML data.
    OK, once you got the data - I suppose in a CLOB or VARCHAR2 variable - convert it to XMLType and use XMLTable function to break the XML into relational rows and columns.
    That way you can directly insert the data into your target table :
    SQL> variable xml_response varchar2(4000)
    SQL> 
    SQL> BEGIN
      2  
      3   :xml_response := '<?xml version="1.0" ?>
      4  <insplist ver="2" count="1">
      5  <insp id="219991" timestamp="134817078" stat="G" operator_id="999999" >
      6  <data name="TIME CLOCK - PUNCH" val="INNER" type="STRING" />
      7  </insp>
      8  </insplist>';
      9  
     10  END;
     11  /
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> SELECT x.*
      2  FROM XMLTable(
      3         '/insplist/insp'
      4         passing xmltype(:xml_response)
      5         columns
      6           insp_id      number       path '@id'
      7         , tstamp       number       path '@timestamp'
      8         , stat         varchar2(1)  path '@stat'
      9         , operator_id  number       path '@operator_id'
     10         , data_name    varchar2(30) path 'data/@name'
     11         , data_val     varchar2(30) path 'data/@val'
     12         , data_type    varchar2(30) path 'data/@type'
     13       ) x
     14  ;
     
       INSP_ID     TSTAMP STAT OPERATOR_ID DATA_NAME                      DATA_VAL                       DATA_TYPE
    ---------- ---------- ---- ----------- ------------------------------ ------------------------------ ------------------------------
        219991  134817078 G         999999 TIME CLOCK - PUNCH             INNER                          STRING
     
  • 4. Re: Read XML data without name spaces from PL/SQL
    794743 Newbie
    Currently Being Moderated
    Hi Odie,

    Thanks for your prompt response and it really worked well!!!!

    I need a one more advice on my logic of pulling XML data thru UTL_HTTP response.
    Currently this requirement is done through Java and my client want to rewrite into Oracle PL/SQL.
    I researched/googled and found out that this is achievable thru UTL_HTTP of get method.

    Can you please throw some light on this.

    Thanks in advance.
  • 5. Re: Read XML data without name spaces from PL/SQL
    Jason_(A_Non) Expert
    Currently Being Moderated
    Here is a good basic example of how to retrieve data from a Web Service via PL/SQL

    [url http://www.liberidu.com/blog/2009/07/14/howto-consume-webservices-via-plsql/]HOWTO: Consume Webservices via PL/SQL
  • 6. Re: Read XML data without name spaces from PL/SQL
    794743 Newbie
    Currently Being Moderated
    Hi,

    This is good and I will work on it based on the concepts..

    Thanks,
  • 7. Re: Read XML data without name spaces from PL/SQL
    odie_63 Guru
    Currently Being Moderated
    And it's even simpler with the GET method as we just have to pass the url :
    SQL> DECLARE
      2  
      3    v_url      varchar2(200) := 'http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&address=';
      4    v_address  varchar2(200) := '1 rue Victor Hugo Paris France';
      5  
      6    v_req      utl_http.req;
      7    v_rsp      utl_http.resp;
      8  
      9  
     10  
     11    v_xml      varchar2(32767);
     12  
     13    v_lng      number;
     14    v_lat      number;
     15  
     16  BEGIN
     17  
     18    v_req := utl_http.begin_request(v_url || utl_url.escape(v_address), 'GET', utl_http.HTTP_VERSION_1_1);
     19    v_rsp := utl_http.get_response(v_req);
     20  
     21    utl_http.read_text(v_rsp, v_xml);
     22    utl_http.end_response(v_rsp);
     23  
     24    select longitude, latitude
     25    into v_lng, v_lat
     26    from xmltable(
     27         '/GeocodeResponse/result/geometry/location'
     28         passing xmlparse(document v_xml)
     29         columns longitude number path 'lng'
     30               , latitude  number path 'lat'
     31         )
     32    ;
     33  
     34    dbms_output.put_line('Longitude = ' || v_lng);
     35    dbms_output.put_line('Latitude = ' || v_lat);
     36  
     37  END;
     38  /
     
    Longitude = 2.2734472
    Latitude = 48.8282741
     
    PL/SQL procedure successfully completed
     
    Or, taking shorcuts :
    SQL> select *
      2  from xmltable(
      3       '/GeocodeResponse/result/geometry/location'
      4       passing httpuritype('http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&address='
      5                           || utl_url.escape('1 rue Victor Hugo Paris France')
      6                           ).getxml()
      7       columns longitude number path 'lng'
      8             , latitude  number path 'lat'
      9       )
     10  ;
     
     LONGITUDE   LATITUDE
    ---------- ----------
     2,2734472 48,8282741
     
  • 8. Re: Read XML data without name spaces from PL/SQL
    794743 Newbie
    Currently Being Moderated
    Hi Odie,

    Awesome...I am speechless..
    Let me try this and update with the results.

    Thank you Once again..
  • 9. Re: Read XML data without name spaces from PL/SQL
    794743 Newbie
    Currently Being Moderated
    Hi Odie,

    I tried the above SQL and I got the result what I want..Now I would like to know,how to do couple of other tasks such as below.

    1.Pass user id & password along with URL
    2.Retrieve the XML data in CLOB.
    3.During XML parsing,If I encounter any error,I need to capture that record in error table.

    Please let me know your suggestions/vision on it.

    Thanks in Advance.
  • 10. Re: Read XML data without name spaces from PL/SQL
    odie_63 Guru
    Currently Being Moderated
    1.Pass user id & password along with URL
    2.Retrieve the XML data in CLOB.
    Points 1 and 2 can be answered by browsing the doc for UTL_HTTP package :
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_http.htm#ARPLS70954

    For 1, see UTL_HTTP.SET_AUTHENTICATION.
    You can also pass user and password directly in the url, e.g. http://user:password@mysite.org/example but you probably don't want to do that.

    For 2, see UTL_HTTP.READ_TEXT to read the response in chunks and build the CLOB.
    This thread will give you some ideas : {thread:id=2375680}

    3.During XML parsing,If I encounter any error,I need to capture that record in error table.
    What kind of errors are you expecting?

    See if "DML Error Logging" suits your need : http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables004.htm#ADMIN10261

    Edited by: odie_63 on 26 sept. 2012 18:34
  • 11. Re: Read XML data without name spaces from PL/SQL
    794743 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the directions. I was able to achieve what I need.
    The question regarding to error, How to handle If any record might turned to error.But it looks like,I am able to handle in my process and I am good on that...

    I Know this could be different subject...But still on the same project what I am currently working on...

    I have a multi-table Main cursor and I need to run the program in multiple background jobs?
    How can I achieve it? Any suggestion are highly welcome..

    Thanks in Advance...
  • 12. Re: Read XML data without name spaces from PL/SQL
    Jason_(A_Non) Expert
    Currently Being Moderated
    That would be a new question since it has nothing to do with the original question of your thread. It might need the use of the DBMS_SCHEDULER over on the {forum:id=193} forum.

    It could also be something else as each session (connection) to the DB can access the program independent of each other, but your description is vague enough I'm not sure which way you are headed.

Legend

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