This discussion is archived
3 Replies Latest reply: Jun 27, 2012 6:35 AM by odie_63 RSS

XML in PLSQL

user10177353 Newbie
Currently Being Moderated
Hi There,
I have the following scenario:
a) The XML data will be stored in TABLE A as a varchar.
b) Need a plsql procedure/function to read the xml from TABLE A and add record in TABLE B. Need to implement this in DOM.

Q. would the XSD & XML solution would be a better one? So that in future if there are any changes made to XSD that would reflect the XML?

Can you please help me with a code snippet, that would be much helpful.

Below is the xml:
Line: -----

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns="http://application/StatusNotification">
<SOAP-ENV:Body>
<StatusNotification>
<StatusNotificationMessage>
<sourceSystem>
<sourceSystemType>Somewhere</sourceSystemType>
<sourceSystemId>MySystem</sourceSystemId>
</sourceSystem>
<alternativeIdentifiers>
<alternativeIdentifier>
<name>employeeNumber</name>
<value>1234567890</value>
</alternativeIdentifier>
<alternativeIdentifier>
<name>departmentNumber</name>
<value>12345678</value>
</alternativeIdentifier>
</alternativeIdentifiers>
<characteristics>
<characteristic>
<name>swipeDateTime</name>
<value>20120613123625</value>
</characteristic>
<characteristic>
<name>role</name>
<value>MAN</value>
</characteristic>
</characteristics>
</StatusNotificationMessage>
</StatusNotification>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Line: -----

Kind regards
  • 1. Re: XML in PLSQL
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Please give your database version : SELECT * FROM v$version.
    a) The XML data will be stored in TABLE A as a varchar.
    It would be better to use an XMLType column (binary XML storage if available in your version)
    b) Need a plsql procedure/function to read the xml from TABLE A and add record in TABLE B. Need to implement this in DOM.
    You "need" DOM? Why is that exactly?

    Which repeating elements represent records in your input XML?
    Is it "alternativeIdentifier", "characteristic", both?

    What's the DDL of your target table?
  • 2. Re: XML in PLSQL
    user10177353 Newbie
    Currently Being Moderated
    Database ver:
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
    PL/SQL Release 9.2.0.8.0 - Production
    CORE     9.2.0.8.0     Production

    TNS for Solaris: Version 9.2.0.8.0 - Production
    NLSRTL Version 9.2.0.8.0 - Production
    -----
    It would be better to use an XMLType column (binary XML storage if available in your version)
    Since the source for the XML will be via Websphere MQ, the Pro*C program will pull the XML from MQ and adds it into TABLE A.

    You "need" DOM? Why is that exactly?
    I was told it's a standard and also useful for future updates to XML or XSD etc, than using the other approach

    Which repeating elements represent records in your input XML? Is it "alternativeIdentifier", "characteristic", both?
    Both. Future there may be some more repeated elements under any of these 2

    What's the DDL of your target table?
    SQL> CREATE TABLE staging
    2 ("employeeNumber" VARCHAR(20),
    3 "departmentNumber" VARCHAR(9),
    4 "swipeDateTime" DATE,
    5 "role" VARCHAR(4))
    6 /

    Regards
  • 3. Re: XML in PLSQL
    odie_63 Guru
    Currently Being Moderated
    I would do it like this :
    SQL> DECLARE
      2  
      3    v_nsmap VARCHAR2(200) := 'xmlns:s="http://schemas.xmlsoap.org/soap/envelope/", xmlns="http://application/StatusNotification"';
      4  
      5  BEGIN
      6  
      7    insert into staging (employeenumber, departmentnumber, swipedatetime, role)
      8    select extractvalue(
      9             value(x)
     10           , '/StatusNotificationMessage/alternativeIdentifiers/alternativeIdentifier[name="employeeNumber"]/value'
     11           , v_nsmap
     12           )
     13         , extractvalue(
     14             value(x)
     15           , '/StatusNotificationMessage/alternativeIdentifiers/alternativeIdentifier[name="departmentNumber"]/value'
     16           , v_nsmap
     17           )
     18         , to_date(
     19             extractvalue(
     20               value(x)
     21             , '/StatusNotificationMessage/characteristics/characteristic[name="swipeDateTime"]/value'
     22             , v_nsmap
     23             )
     24           , 'YYYYMMDDHH24MISS'
     25           )
     26         , extractvalue(
     27             value(x)
     28           , '/StatusNotificationMessage/characteristics/characteristic[name="role"]/value'
     29           , v_nsmap
     30           )
     31    from table_a t
     32       , table(
     33           xmlsequence(
     34             extract(
     35               xmltype(t.soap_response)
     36             , '/s:Envelope/s:Body/StatusNotification/StatusNotificationMessage'
     37             , v_nsmap
     38             )
     39           )
     40         ) x
     41    ;
     42  
     43  END;
     44  /
     
    PL/SQL procedure successfully completed
     
    SQL> select * from staging
      2  ;
     
    EMPLOYEENUMBER       DEPARTMENTNUMBER SWIPEDATETIME ROLE
    -------------------- ---------------- ------------- ----
    1234567890           12345678         13/06/2012 12 MAN
     
    The DOM approach would look like :
    SQL> DECLARE
      2  
      3    v_soap_response     table_a.soap_response%type;
      4  
      5    v_doc               dbms_xmldom.DOMDocument;
      6    v_message_node      dbms_xmldom.DOMNode;
      7    v_nsmap             varchar2(200) := 'xmlns="http://application/StatusNotification"';
      8    v_staging_rec       staging%rowtype;
      9  
     10  BEGIN
     11  
     12    select soap_response
     13    into v_soap_response
     14    from table_a;
     15  
     16    v_doc := dbms_xmldom.newDOMDocument(v_soap_response);
     17  
     18    v_message_node := dbms_xslprocessor.selectSingleNode(
     19                        dbms_xmldom.makeNode(v_doc)
     20                      , '//StatusNotificationMessage'
     21                      , v_nsmap
     22                      );
     23  
     24    dbms_xslprocessor.valueOf(
     25      v_message_node
     26    , 'alternativeIdentifiers/alternativeIdentifier[name="employeeNumber"]/value'
     27    , v_staging_rec.employeeNumber
     28    , v_nsmap
     29    );
     30  
     31    dbms_xslprocessor.valueOf(
     32      v_message_node
     33    , 'alternativeIdentifiers/alternativeIdentifier[name="departmentNumber"]/value'
     34    , v_staging_rec.departmentNumber
     35    , v_nsmap
     36    );
     37  
     38    v_staging_rec.swipeDateTime :=
     39    to_date(
     40      dbms_xslprocessor.valueOf(
     41        v_message_node
     42      , 'characteristics/characteristic[name="swipeDateTime"]/value'
     43      , v_nsmap
     44      )
     45    , 'YYYYMMDDHH24MISS'
     46    );
     47  
     48    dbms_xslprocessor.valueOf(
     49      v_message_node
     50    , 'characteristics/characteristic[name="role"]/value'
     51    , v_staging_rec.role
     52    , v_nsmap
     53    );
     54  
     55    insert into staging values v_staging_rec;
     56  
     57    dbms_xmldom.freeNode(v_message_node);
     58    dbms_xmldom.freeDocument(v_doc);
     59  
     60  END;
     61  /
     
    PL/SQL procedure successfully completed
     
    SQL> select * from staging;
     
    EMPLOYEENUMBER       DEPARTMENTNUMBER SWIPEDATETIME ROLE
    -------------------- ---------------- ------------- ----
    1234567890           12345678         13/06/2012 12 MAN
     

Legend

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