BizTalk Utilities CV ,   Jobs ,   Code library
 
Home Page


Add/Edit your code items
Search the code library
Browse for the code library

SQL XML

SQL XML

SQL XML Contents

Download data from a database in XML
Inserting XML file into a table using DBMS_XMLSave Package
Insert XML file into a table using ADO
How to call out to a COM component's method through XSLT
How to select DISTINCT items from XML via XSLT
How to convert Word (RTF) documents to XML for auto publication
Creating SQL Statements with XSLT
SQL straight to XML w/ transform
History Of XML
History Of XML And What Is XML.
Order Automation
You enjoy while you are away and let your machine do the job for you.
SQL Server 2000 User Defined Functions - A Powerful concept
XML/XSLT Maker
Generic ADO recordset to HTML table using XML / XSL
How to display the first three nodes of a XML file with XmlDocument?
What is the usage of the XmlDataDocument?
 
 

<< SEOSystem.XML >>

By Pedro Gil
First Posted 01/03/2002
Times viewed 190

DBMS_XMLQuery package bind values to produce more than one xml file

Summary DBMS_XMLQuery package bind values to produce more than one xml file

I'm using Oracle 8.1.7 and the XDK 9.0.1.1.0A for PL/SQL

You can get the latest version of the XDK at OTN

http://otn.oracle.com/software/tech/xml/xdk_plsql/content.html

One common problem that I face in some applications is the need to produce different xml files based on the same query, for instance you want to produce a file with all the orders for a particular customer. One approach would be to make a procedure that receives a parameter with the customer number and produce the xml file for that customer.

But sometimes you need to make this for several customers in a row, off course that you can call that procedure several times, but that will fall in some overhead of creating the context every time and parsing the SQL statement.

Or you can use this approach, what I've made is the following:

I created one object that will work like an parameter array for the procedure.

CREATE OR REPLACE TYPE t_cust IS TABLE OF NUMBER;

Now you can have a procedure that get as parameter the object t_cust

CREATE OR REPLACE PROCEDURE SeveralResults (p_cust IN t_cust) IS

queryCtx DBMS_XMLquery.ctxType;

result CLOB;

i NUMBER := 1;

BEGIN

queryCtx := DBMS_XMLQuery.newContext('SELECT * FROM Orders WHERE CustID = :pCust');

FOR i IN 1..p_cust.COUNT LOOP

--Bind the value of i to pCust on the queryCtx

DBMS_XMLQuery.setBindValue(queryCtx,'pCust',p_cust(i));

result := DBMS_XMLQuery.getXML(queryCtx);

Clob2table(result);

END LOOP;

DBMS_XMLQuery.closeContext(queryCtx);

END;

The use of the SetBindValue will avoid the reparsing of the select statement.

Now you can call the procedure SeveralResults like this

BEGIN

SEVERALRESULTS(t_cust(100,108));

END;

This would produce two diferent clobs one for each customer passed on as parameter.

Pedro Gil

Rate this article on a scale of 1 to 10 (0 votes, average 0)

Your vote :  

<< SEOSystem.XML >>



Leave a comment for this article
Your name
Your email (optional)
Your comment
Optional: Upload an attachment
Enter the code shown:

 
 

    Email TopXML