Parsing XML using Oracle Objects(Types)

In the back to back system integrations, the common transmission language is the XML, since it is supported by most of the platforms, easy to understand and implement.

In Oracle, if there is a requirements to receive message body as XML, and this message structure is pre defined, and both parties, sender and receiver are aware of this, we can use the following technique to parse it and make use of the data.

Lets' say we have an XML coming as the following:

<PERSON_PROFILE>

  <FIRSTNAME>Mohammad</FIRSTNAME>

  <SECONDNAME>Ahmed</SECONDNAME>

  <LASTNAME>Abdullah</LASTNAME>

  <PERSONID>1231789456</PERSONID>

  <QUALIFICATION_LST>

    <QUALIFICATION>

      <QUAL_NAME>basic school</QUAL_NAME>

      <QUAL_DESC> basic school</QUAL_DESC>

    </QUALIFICATION>

    <QUALIFICATION>

      <QUAL_NAME>secondary school</QUAL_NAME>

      <QUAL_DESC> secondary school</QUAL_DESC>

    </QUALIFICATION>

    <QUALIFICATION>

      <QUAL_NAME>Bs Degree</QUAL_NAME>

      <QUAL_DESC> Bs Degree</QUAL_DESC>

    </QUALIFICATION>

  </QUALIFICATION_LST>

</PERSON_PROFILE>

First thing we will do, we will define an oracle types( Objects ) and tables that has the same structure of the XML root element:

--CREATE OBJECT TO HOLD THE QUALIFICATION INFO

CREATE OR REPLACE TYPE "QUALIFICATION_OBJ" AS OBJECT (

   QUAL_NAME   VARCHAR2 (100),

   QUAL_DESC   VARCHAR2 (1000)

);

/

--CREATE TABLE OF OBJECTS TO HOLE THE MULTI OBJECT VALUES

CREATE OR REPLACE TYPE "QUALIFICATION_LIST_TBL" AS TABLE OF QUALIFICATION_OBJ;

/

--CREATE THE ROOT OBJECT THAT REPRESENT THE WHOLE XML STRUCTURE

CREATE OR REPLACE TYPE "PERSONPROFILE_OBJ" AS OBJECT (

   FIRSTNAME            VARCHAR2 (200),

   SECONDNAME           VARCHAR2 (200),

   LASTNAME             VARCHAR2 (200),

   PERSONID             NUMBER (16),

   QUALIFICATION_LIST   QUALIFICATION_LIST_TBL

);

CREATE TABLE "PERSON_TABLE"  (

   ID NUMBER PRIMARY KEY,FIRSTNAME            VARCHAR2 (200),

   SECONDNAME           VARCHAR2 (200),

   LASTNAME             VARCHAR2 (200),

   PERSONID             NUMBER (16)

);

CREATE TABLE "QUALIFICATION_TABLE" (

   QUAL_NAME   VARCHAR2 (100),

   QUAL_DESC   VARCHAR2 (1000),

   PERSON_ID NUMBER ,FOREIGN KEY(PERSON_ID) REFERENCES PERSON_TABLE(ID)

);

Now we have the structure ready to accept the information from XML, we will create a procedure to be called and use these objects.

CREATE OR REPLACE PROCEDURE CREATEPERSONPROFILE_PRC (

   PX_MSG_IN    IN       CLOB,

   PX_MSG_OUT   OUT      CLOB

)

AS

   L_MYOBJECT   PERSONPROFILE_OBJ;

   L_XMLTYPE    XMLTYPE;

   OUT_XML      XMLTYPE;

BEGIN

   L_XMLTYPE := XMLTYPE (PX_MSG_IN);

   L_XMLTYPE.TOOBJECT (L_MYOBJECT);

   INSERT INTO PERSON_TABLE

               (FIRSTNAME, SECONDNAME,

                LASTNAME, PERSONID

               )

        VALUES (L_MYOBJECT.FIRSTNAME, L_MYOBJECT.SECONDNAME,

                L_MYOBJECT.LASTNAME, L_MYOBJECT.PERSONID

               );

   FOR I IN (SELECT VALUE (TD) OBJ_IN_COL

               FROM TABLE (L_MYOBJECT.QUALIFICATION_LIST) TD)

   LOOP

      INSERT INTO QUALIFICATION_TABLE

                  (QUAL_NAME, QUAL_DESC,PERSON_ID 

                  )

           VALUES (I.OBJ_IN_COL.QUAL_NAME, I.OBJ_IN_COL.QUAL_DESC,
L_MYOBJECT.PERSONID

                  );

   END LOOP;

END;

PS. The code is not based on the standards and best practices, it was only to show the concept.