Oracle SQL - XML Builder [ was : Is this Impossible !!]

Steve Muench smuench at
Fri Oct 22 09:01:32 BST 1999


The release notes cover the options you can
provide in your <query> tag to control the
raw database XML formatting of the query results,
but the arbitrary reshaping power to really
get the data to morph into any DTD shape comes
by combining the XML "datapage" produced by
your SQL query with an XSLT transformation
that gets the data exactly how you want it.

My example will use the XSQL Servlet
since it's easier to explain that way,
but you can do this in your own code
too if the XSQL Servlet is not what
you want. Let's say your Customer.xsql page
looks like:

<?xml version="1.0"?>
<query connection="prodb">
  SELECT spname,
   FROM your_customer_table
  WHERE custpermid = {@custid}

Then by default the request for the URL:

will produce exactly the result you had in your mail.

If however you have a DTD you need to refer to

You can create the following XSL Stylesheet:

<xsl:stylesheet xmlns:xsl="">
<xsl:output indent="yes" doctype-system=""/
<xsl:template match="ROWSET/ROW[1]">
<Customer id="{CUSTPERMID}" Name="{SPNAME}" >
       <Given><xsl:value-of select="FIRSTNAME"/></Given>
       <Surname><xsl:value-of select="LASTNAME"/></Surname>
     <Email><xsl:value-of select="CUSTLOGINID"/></Email>

And then alter your Customer.xsql page to have
one extra <?xml-stylesheet?> instruction at the top

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="ToCustomerDTD.xsl"?>
<query connection="proddb">
  SELECT spname,
   FROM your_customer_table
  WHERE custpermid = {@custid}

And then when you request the same URL now, you'll receive:

<?xml version = '1.0' encoding = 'UTF-8'?>
<!DOCTYPE Customer SYSTEM "">
<Customer id="123456789123456" Name="Huntington services Company">

If you're not using XSQL Pages to do this, then
you can use the same stylesheet along with the
getXMLDOM() method of the OracleXMLQuery class
you are using to do:

  OracleXMLQuery q = new OracleXMLQuery( conn, yourResultSet);
                     // or OracleXMLQuery( conn, yourQueryString );
  Document        d = yourQuery.getXMLDOM();
  XSLStylesheet xsl = new XSLStylesheet("ToCustomerDTD.xsl", null);
  XSLProcessor  prc = new XSLProcessor();
  prc.process(d,xsl, new PrintWriter(System.out));
  // or DocumentFragment result = prc.process(d,xsl);

You can use the companion OracleXMLSave class to *insert* XML
into your tables, views, object tables, and object views of
arbitrary structure, too.

Hope this sample helps...

Steve Muench, BC4J Development Team & XML Evangelist
----- Original Message -----
From: Abhishek Srivastava <abisheks at>
To: xml dev mailing list <xml-dev at>
Sent: Thursday, October 21, 1999 10:52 PM
Subject: Oracle SQL - XML Builder [ was : Is this Impossible !!]

Hi ,

I tried the Oracle's SQL - XML utility and it does what i want.  Except a
few things.

I generated the following XML file as a result to a query to my oracle

 <ROW num="1">
  <SPNAME>Huntington services Company             </SPNAME>
  <CUSTPERMID>123456789123456     </CUSTPERMID>
  <CUSTLOGINID>jamesb              </CUSTLOGINID>

The ROWSET and the ROW tags were introduced by the builder itself. I would
like the document to look like :

<SPNAME>My Own Business  Services Company  </SPNAME>
  <CUSTPERMID>123456789123456     </CUSTPERMID>
  <CUSTLOGINID>jamesb              </CUSTLOGINID>
  <FIRSTNAME>james               </FIRSTNAME>
  <LASTNAME>Bond                </LASTNAME>

This is very important because this xml file will be sent to the client who
will "validate" it. Since ROWSET and ROW are not a part of standard message
format we use... an exception will be thrown.
Is there any way to customize the output from the builder so that it
conforms to a predefined DTD ?

    _/               Abhishek Srivastava
   _/                Hewlett Packard ISO
  _/_/_/   _/_/_/    -------------------
 _/   /   _/  _/     (Work)   +91-80-2251554 x1190
_/  _/   _/_/_/      (Ip)
        _/           (Url)
       _/            You've heard it all by now. Get wired or get whacked.
                     You're networking or you're not working. Dot-com or die
                     - SUN MICROSYSTEMS

xml-dev: A list for W3C XML Developers. To post, mailto:xml-dev at
Archived as: and on CD-ROM/ISBN 981-02-3594-1
To unsubscribe, mailto:majordomo at the following message;
unsubscribe xml-dev
To subscribe to the digests, mailto:majordomo at the following message;
subscribe xml-dev-digest
List coordinator, Henry Rzepa (mailto:rzepa at

More information about the Xml-dev mailing list