3 Replies Latest reply: Jun 27, 2012 8:35 AM by odie_63 RSS

    XML in PLSQL

    user10177353
      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
          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
            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
              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