<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Generate a Static XML using Simple Oracle Tables.

Published on
9,071 Points
2,271 Views
3 Endorsements
Last Modified:
Approved
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
)

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>

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

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;

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

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;

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
)

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;
/

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
)

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.
3
  • 3
  • 3
6 Comments
LVL 80

Expert Comment

by:slightwv (䄆 Netminder)
>>without using any of the complex XML queries.

Personally I find this WAY more complex than any of the XML functions but to each his own.


This has some minor issues and some pretty major ones.

First:
The function uses xxcrm_xml_templates and xxcrm_xml_template_dtls
The create table examples use XXCUSTOM_XML_TEMPLATES and XXCUSTOM_XML_TEMPLATE_DTLS

CRM vs CUSTOM

Now the major one:
Just because it has start tags and end tags doesn't mean it is XML.

It's a pretty simple matter to have it generate invalid XML.

For example:
change:
   (1, 'TOTAL', 'select 75 from dual');
to:
   (1, 'TOTAL', 'select ''THIS&THAT'' from dual');

change:
   (1, 'ABC', 'select ''ABC'' from dual');
to:
   (1, 'ABC', 'select ''A B C'' from dual');


Rerun the code and you have invalid XML.

Could be more problems but I'm not going to debug the entire thing.
0
LVL 16

Author Comment

by:Wasim Akram Shaik
Leaving this aside >>without using any of the complex XML queries.

Thanks Steve pointing  out the typo mistakes.

Had corrected them and also to answer what you had pointed out

   (1, 'ABC', 'select ''A B C'' from dual');

This will work fine..

only the problematic special characters are <,>,&,',"

they have to be worked out in select query of the element itself by replacing them with appropriate xml equivalent characters, else we can write a separate function to do this task

taking what you mentioned in comment

   (1, 'TOTAL', 'select ''THIS&THAT'' from dual');

CREATE or replace function xxcustom_special_character return varchar2 as
var varchar2(100);
begin
select replace(replace('"THIS&THAT"',chr(38),chr(38)||'amp'),chr(34),chr(38)||'quot'||';') into var from dual;
--dbms_output.put_line('var'||var);
return var;
end;

Open in new window


after creation

replace the element query with  this

select apps.xxcustom_special_character from dual

By this way, these special characters too can be handled.

Would appreciate if you review and suggest if there are any further recommendations.
0
LVL 80

Expert Comment

by:slightwv (䄆 Netminder)
I'm afraid 'simple' changes and tweaks to this article will not fix the fundamental flaws with the methodology.

You would have to go through the entire w3 XML spec to make sure your code complies with ALL the rules.

My personal opinion is that the XML functions are far simpler than what you are proposing as easier.

Also, although "<a>60<b>10</b></a>" passes some of the XML validators (Even Oracle will allow it) it is considered by many to be invalid.

The reason:  A node should not be a text node and parent node at the same time.

It might even be invalid in the XML spec.  I've not read it end-to-end.

>>by replacing them with appropriate xml equivalent characters

Not quite that simple.

Using your original code with your new replace suggestion:
 (1, 'ABC', 'select ''ABC'' from dual');
with:
 (1, 'ABC', 'select ''THIS&THAT'' from dual');

>>you had pointed out    (1, 'ABC', 'select ''A B C'' from dual');  This will work fine..

It generates output but that output isn't valid XML.

Make the change, run it and paste the results into a validator.

>>Would appreciate if you review and suggest if there are any further recommendations.

Sorry for being harsh here but:
My only suggestion would be to rethink this as even a remote possibility of a replacement to the XML functions.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LVL 16

Author Comment

by:Wasim Akram Shaik
Steve, I completely agree with you and I never mentioned that this is a replacement for Oracle XML utilities

I just said that a Simple XML can be generated using oracle tables without using any xml functions

>>you had pointed out    (1, 'ABC', 'select ''A B C'' from dual');  This will work fine..

 To illustrate this, I had showed the content of the xml generator validated via xml.exe utility(in built utility with oracle database generally present under bin folder, and also via jdeveloper)


<?xml version="1.0"?>
-<SCHOOL_ABC>
75
-<CLASS1>
    30
        <SECTION1>10</SECTION1>
        <SECTION2>20</SECTION2>
</CLASS1>
<CLASS2>20</CLASS2>
<CLASS3>"THIS\&THAT"</CLASS3>
</SCHOOL_ABC>

Open in new window


generated xml is

<SCHOOL_ABC>75<CLASS1>30<SECTION1>10</SECTION1><SECTION2>20</SECTION2></CLASS1><CLASS2>20</CLASS2><CLASS3>&quot;THIS\&amp;THAT&quot;</CLASS3></SCHOOL_ABC>

And if you see the basic logic of how this works, actually its not at all generating any XML, its using a template kind of thing and its just replacing the values which are generated dynamically by using NDS.

So its up to the template to maintain the XML Standard which we insert in the template table.

This kind of approach would be handy(quick to implement and saves considerable development effort) in case we need to integrate with (for suppose) to 100 third party systems where in the middle ware has a limited capability, then a single interface can be built based on this in less time.
0
LVL 80

Expert Comment

by:slightwv (䄆 Netminder)
This is my last comment on this article.

I stand by what I said.

I would caution anyone reading this article that is dealing with XML: Use the Oracle XML functions and not a bunch of string replacements and call it XML.
0
LVL 16

Author Comment

by:Wasim Akram Shaik
Thanks for your comments. I take them in good spirit.

This too is my last comment, to re-iterate that this is just a mechanism to generate xml using tables not anything against oracle xml utilities.

Many people will differ in their thoughts on a same thing, this doesn't make one is correct and other is wrong. They are just different. Same applies here.
0

Featured Post

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month