Generate XML Based on a SQL Query

Compliments of Nilesh Chindarkar (Nilesh.chindarker@lvl.co.uk)

The following package will take a SQL query as input and generate an XML document using the PL/SQL XML API. It stores the data in a CLOB, the writes the data from the CLOB to an output file – output.xml. Uncomment the dbms_output lines to view the output on screen. Note that the package requires Oracle 8i and Oracle XDK Java 9.2.0.2.0.
CREATE OR REPLACE PACKAGE pkg_plsql_xml
IS

/*This procedure will write the content from CLOB to output file output.xml */

PROCEDURE prc_print (p_clob IN CLOB);

/*This procedure will takes the SQL query as input and */
/*using PL/SQL XML API generates the XML doc */

PROCEDURE prc_gen_xml (
p_sql IN VARCHAR2,
p_rowset_tag IN VARCHAR2 DEFAULT NULL,
p_row_tag IN VARCHAR2 DEFAULT NULL,
p_rowid_attr_name IN VARCHAR2 DEFAULT NULL,
p_rowid_attr_value IN VARCHAR2 DEFAULT NULL,
p_collidattrname IN VARCHAR2 DEFAULT NULL,
p_usenullattributeindicator IN BOOLEAN DEFAULT NULL,
p_dateformat IN VARCHAR2 DEFAULT NULL,
p_maxrows IN NUMBER DEFAULT NULL,
p_skiprows IN NUMBER DEFAULT NULL
);
END pkg_plsql_xml;
/

CREATE OR REPLACE PACKAGE BODY pkg_plsql_xml
IS
PROCEDURE prc_print (p_clob IN CLOB)
IS
result CLOB;
cvl_tmp VARCHAR2 (32000);
nvl_amount NUMBER := 255; –32000;
nvl_pos NUMBER := 1;
nvl_clob_length NUMBER;
instr_pos NUMBER;
file_handle UTL_FILE.file_type;
v_file_location VARCHAR2 (30) := ‘g:\batch\files’;
v_filename VARCHAR2 (50) := ‘Output.XML’;
BEGIN
file_handle := UTL_FILE.fopen (v_file_location, v_filename, ‘W’);
result := p_clob;

———————————————————
–write clob to file
———————————————————
nvl_clob_length := DBMS_LOB.getlength (result);
cvl_tmp := NULL;
nvl_amount := 255; –32000;
nvl_pos := 1;

LOOP
instr_pos :=
DBMS_LOB.INSTR (result, CHR (10), nvl_pos, 1)
– nvl_pos;

— DBMS_OUTPUT.PUT_LINE(nvl_pos||’: Of length : ‘||instr_pos);
IF nvl_pos
+ instr_pos > nvl_clob_length
THEN
instr_pos := nvl_clob_length
– nvl_pos;
DBMS_LOB.READ (
lob_loc=> result,
amount=> instr_pos,
offset=> nvl_pos,
buffer=> cvl_tmp
);
EXIT;
END IF;

DBMS_LOB.READ (
lob_loc=> result,
amount=> instr_pos,
offset=> nvl_pos,
buffer=> cvl_tmp
);
— DBMS_OUTPUT.PUT_LINE(cvL_tmp);

UTL_FILE.put_line (file_handle, cvl_tmp);
nvl_pos := nvl_pos
+ instr_pos
+ 1;

IF nvl_pos > nvl_clob_length
THEN
EXIT;
END IF;
END LOOP;

UTL_FILE.fclose (file_handle);
END prc_print;

PROCEDURE prc_gen_xml (
p_sql IN VARCHAR2,
p_rowset_tag IN VARCHAR2 DEFAULT NULL,
p_row_tag IN VARCHAR2 DEFAULT NULL,
p_rowid_attr_name IN VARCHAR2 DEFAULT NULL,
p_rowid_attr_value IN VARCHAR2 DEFAULT NULL,
p_collidattrname IN VARCHAR2 DEFAULT NULL,
p_usenullattributeindicator IN BOOLEAN DEFAULT NULL,
p_dateformat IN VARCHAR2 DEFAULT NULL,
p_maxrows IN NUMBER DEFAULT NULL,
p_skiprows IN NUMBER DEFAULT NULL
)
IS
queryctx dbms_xmlquery.ctxtype;
result CLOB;
v_str VARCHAR2 (4000);
BEGIN
— set up the query context…!
queryctx := dbms_xmlquery.newcontext (p_sql);

IF p_rowset_tag IS NOT NULL
THEN
dbms_xmlquery.setrowsettag (ctxhdl => queryctx, tag => p_rowset_tag);
END IF;

IF p_row_tag IS NOT NULL
THEN
dbms_xmlquery.setrowtag (ctxhdl => queryctx, tag => p_row_tag);
END IF;

IF p_rowid_attr_name IS NOT NULL
THEN
dbms_xmlquery.setrowidattrname (
ctxhdl=> queryctx,
attrname=> p_rowid_attr_name
);
END IF;

IF p_rowid_attr_value IS NOT NULL
THEN
dbms_xmlquery.setrowidattrvalue (
ctxhdl=> queryctx,
colname=> p_rowid_attr_value
);
END IF;

IF p_collidattrname IS NOT NULL
THEN
dbms_xmlquery.setcollidattrname (
ctxhdl=> queryctx,
attrname=> p_collidattrname
);
END IF;

IF p_usenullattributeindicator IS NOT NULL
THEN
dbms_xmlquery.usenullattributeindicator (
ctxhdl=> queryctx,
flag => p_usenullattributeindicator
);
END IF;

IF p_dateformat IS NOT NULL
THEN
dbms_xmlquery.setdateformat (
ctxhdl=> queryctx,
mask => p_dateformat
);
END IF;

IF p_maxrows IS NOT NULL
THEN
dbms_xmlquery.setmaxrows (ctxhdl => queryctx, ROWS => p_maxrows);
END IF;

IF p_skiprows IS NOT NULL
THEN
dbms_xmlquery.setskiprows (ctxhdl => queryctx, ROWS => p_skiprows);
END IF;

— get the result..!
result := dbms_xmlquery.getxml (queryctx);
— Now you can use the result to put it in tables/send as messages..

— Read CLOB to generate the XML
prc_print (p_clob => result);
dbms_xmlquery.closecontext (queryctx); — you must close the query handle..
END prc_gen_xml;
END pkg_plsql_xml;
/
Usage Examples:
execute pkg_plsql_xml.prc_gen_xml(‘SELECT * FROM EMP’);
execute pkg_plsql_xml.prc_gen_xml(P_SQL => ‘SELECT * FROM
EMP’,p_useNullAttributeIndicator=>True);
I.E:
PROCEDURE prc_gen_xml
(
p_sql IN VARCHAR2,
p_rowset_tag IN VARCHAR2 DEFAULT
NULL,
p_row_tag IN VARCHAR2
DEFAULT NULL,
p_rowid_attr_name IN VARCHAR2 DEFAULT
NULL,
p_rowid_attr_value IN VARCHAR2 DEFAULT
NULL,
p_collidattrname IN VARCHAR2 DEFAULT
NULL,
p_useNullAttributeIndicatorIN BOOLEAN DEFAULT NULL,

p_dateformat IN VARCHAR2 DEFAULT
NULL,
p_maxrows IN NUMBER
DEFAULT NULL,
p_skiprows IN NUMBER
DEFAULT NULL
);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s