Generate a Static XML using Simple Oracle Tables.

This article presents a way to generate a static xml in run time without using any of the complex XML queries.

In short, you will not have the need to use any of Oracle XML based or XML utilities or functions to generate an XML, you just use string(varchar2) variables and few tables to generate an XML in run time.

By doing it in this way, you can generate any type of Static XML, Remember the word Static which i had used over here, you will come to know why i had termed it as Static at the end of this article.

This whole activity only comprises of total 3 database objects
2 Tables and 1 Procedure or Function

Create a table where in you would define a XML Structure, ie., the type/body of XML you want to generate.

CREATE TABLE XXCUSTOM_XML_TEMPLATES
                      (
                        TEMPLATE_ID  NUMBER,
                        XMLSTRUCT    CLOB
                      )

Select all Open in new window

Suppose I want to generate an XML like this

<SCHOOL_ABC>60<CLASS1>30<SECTION1>10</SECTION1><SECTION2>20</SECTION2></CLASS1><CLASS2>15</CLASS2><CLASS3>15</CLASS3></SCHOOL_ABC>

Select all Open in new window

The XML Presents the total strength of the school and the strength of students in the each class(total=class1+class2+class3)
class1=section1+section2

I want to generate this kind of XML's for various schools, the values and their totals may change in run time depending on the school.

For this I would generate an XML template which would resemble like this

<SCHOOL_$$ABC$$>$$TOTAL$$<CLASS1>$$CLASS1_TOTAL$$<SECTION1>$$SECTION1_TOTAL$$</SECTION1><SECTION2>$$SECTION2_TOTAL$$</SECTION2></CLASS1><CLASS2>$$CLASS2_TOTAL$$</CLASS2><CLASS3>$$CLASS3_TOTAL$$</CLASS3></SCHOOL_$$ABC$$> 

Select all Open in new window

Lets insert a record in template table using the code below

SET DEFINE OFF;
                      Insert into XXCUSTOM_XML_TEMPLATES
                         (TEMPLATE_ID, XMLSTRUCT)
                       Values
                         (1, '<SCHOOL_$$ABC$$>$$TOTAL$$<CLASS1>$$CLASS1_TOTAL$$<SECTION1>$$SECTION1_TOTAL$$</SECTION1><SECTION2>$$SECTION2_TOTAL$$</SECTION2></CLASS1><CLASS2>$$CLASS2_TOTAL$$</CLASS2><CLASS3>$$CLASS3_TOTAL$$</CLASS3></SCHOOL_$$ABC$$>');
                      COMMIT;

Select all Open in new window

Where in $$ stands for the run time value which will be change based on query.

Now you need to create the template details table like this

CREATE TABLE XXCUSTOM_XML_TEMPLATE_DTLS
                      (
                        TEMPLATE_ID   NUMBER,
                        ELEMENTVAL    VARCHAR2(100 BYTE),
                        ELEMENTQUERY  VARCHAR2(100 BYTE)
                      )

Select all Open in new window

This table will keep hold of the details of the run time variables and the query which will generate the output of these run time variables.

Lets insert records in these template tables using the below code

SET DEFINE OFF;
                      Insert into XXCUSTOM_XML_TEMPLATE_DTLS
                         (TEMPLATE_ID, ELEMENTVAL, ELEMENTQUERY)
                       Values
                         (1, 'TOTAL', 'select 75 from dual');
                      Insert into XXCUSTOM_XML_TEMPLATE_DTLS
                         (TEMPLATE_ID, ELEMENTVAL, ELEMENTQUERY)
                       Values
                         (1, 'ABC', 'select ''ABC'' from dual');
                      Insert into XXCUSTOM_XML_TEMPLATE_DTLS
                         (TEMPLATE_ID, ELEMENTVAL, ELEMENTQUERY)
                       Values
                         (1, 'CLASS1_TOTAL', 'select 30 from dual');
                      Insert into XXCUSTOM_XML_TEMPLATE_DTLS
                         (TEMPLATE_ID, ELEMENTVAL, ELEMENTQUERY)
                       Values
                         (1, 'SECTION1_TOTAL', 'select 10 from dual');
                      Insert into XXCUSTOM_XML_TEMPLATE_DTLS
                         (TEMPLATE_ID, ELEMENTVAL, ELEMENTQUERY)
                       Values
                         (1, 'SECTION2_TOTAL', 'select 20 from dual');
                      Insert into XXCUSTOM_XML_TEMPLATE_DTLS
                         (TEMPLATE_ID, ELEMENTVAL, ELEMENTQUERY)
                       Values
                         (1, 'CLASS2_TOTAL', 'select 20 from dual');
                      Insert into XXCUSTOM_XML_TEMPLATE_DTLS
                         (TEMPLATE_ID, ELEMENTVAL, ELEMENTQUERY)
                       Values
                         (1, 'CLASS3_TOTAL', 'select 25 from dual');
                      COMMIT;

Select all Open in new window

Now we come to the dynamic xml generation part using these above tables.

For that we have to create one function in our database and the code for the same is shown below

I had created a table XXCUSTOM_XML_OUTPUT just for storing the value of this XML generated in a simple table which i would truncate , this is not needed, you can ignore or comment out if that's not needed.

just create the table so as the below function gets compiled without any errors,

CREATE TABLE XXCUSTOM_XML_OUTPUT
                      (
                        COL1  CLOB
                      )

Select all Open in new window

This output table creation can be skipped by commenting the respective code below if not needed.

CREATE OR REPLACE FUNCTION xxcustom_dyn_xml_generator (
                         p_template_id   IN   NUMBER
                      )
                         RETURN CLOB
                      AS
                         CURSOR c1
                         IS
                            SELECT *
                              FROM xxcustom_xml_template_dtls
                             WHERE template_id = p_template_id;
                      
                         v_xml     CLOB;
                         v_res     VARCHAR2 (100);
                         var_sql   VARCHAR2 (1000);
                       
                         PROCEDURE xx_pragma_transaction (p_in_xml CLOB)
                         IS
                            PRAGMA AUTONOMOUS_TRANSACTION;
                         BEGIN
                            EXECUTE IMMEDIATE 'TRUNCATE TABLE XXCUSTOM_XML_OUTPUT';
                      
                            INSERT INTO xxcustom_xml_output
                                 VALUES (v_xml);
                      
                            COMMIT;
                         END;
                      BEGIN
                         SELECT xmlstruct
                           INTO v_xml
                           FROM xxcustom_xml_templates
                          WHERE template_id = p_template_id;
                      
                         FOR l1 IN c1
                         LOOP
                            var_sql := l1.elementquery;
                      
                            EXECUTE IMMEDIATE var_sql
                                         INTO v_res;
                      
                            -- DBMS_OUTPUT.put_line (
                            --  'var_res for' || l1.elementval || ' is:' || v_res
                            --);
                            v_xml := REPLACE (v_xml, '$$' || l1.elementval || '$$', v_res);
                         END LOOP;
                      
                         xx_pragma_transaction (v_xml);
                         RETURN v_xml;
                      END;
                      /

Select all Open in new window

Now after all this is done. we can generate the XML using a simple select

select xxcustom_dyn_xml_generator(1) from dual

Now lets see a real time scenario where in this could be of good use.

Consider a payment system where in i have to accept payments posted and transfer it to some other system via some interface which needs XML inputs

For a payment transaction, I would have some set of constant transaction identifier such as transaction_id, payment_amount, payment_date, payment_place etc,

The XML which needs to be passed to external system might have a template like this...

<XML><transaction_id>XX12XX</transaction_id><payment_amount>100</payment_amount><payment_place>India</payment_place><payment_date>28-Mar-2014</XML>

You can modify the dynamic generator procedure to have one more input variable such as

CREATE OR REPLACE FUNCTION xxcustom_dyn_xml_generator (
                         p_template_id   IN   NUMBER,p_input_var in varchar2
                      )

Select all Open in new window

Now in the line       EXECUTE IMMEDIATE var_sql
                   INTO v_res;
you can use the bind variable       EXECUTE IMMEDIATE var_sql
                   INTO v_res using p_input_var;

where in you will have queries stored in xxcrm_xml_template_details table such as

select <column_value> from <table_name> where transaction_id=:p_input_var

By this way, you can generate any real time XML just adding it to configuration tables which we have created.

However as said earlier this is useful in case of Static XML where you have simple XML elements and you are aware that XML element will not repeat for n number of times, that's what I refer static.