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,
)
VALUES (I.OBJ_IN_COL.QUAL_NAME, I.OBJ_IN_COL.QUAL_DESC,
);
END LOOP;
END;
PS. The code is not based on the standards and best practices, it was only to show the concept.
