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

[Steve Muench]

Sure,


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.

http://technet.oracle.com/tech/xml/xsql_servlet/htdocs/relnotes.htm
http://technet.oracle.com/docs/tech/xml/oracle_xsu/doc_library/relnotes.html

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,
         custpermid,
         custloginid,
         firstname,
         lastname
   FROM your_customer_table
  WHERE custpermid = {@custid}
</query>

Then by default the request for the URL:

http://yourbox.com/Customer.xsql?custid=123456789123456

will produce exactly the result you had in your mail.

If however you have a DTD you need to refer to
like:   http://xmlville.com/customer.dtd

You can create the following XSL Stylesheet:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes" doctype-system="http://xmlville.com/customer.dtd"/
<xsl:template match="ROWSET/ROW[1]">
<Customer id="{CUSTPERMID}" Name="{SPNAME}" >
  <PrimaryContact>
     <Name>
       <Given><xsl:value-of select="FIRSTNAME"/></Given>
       <Surname><xsl:value-of select="LASTNAME"/></Surname>
     </Name>
     <Email><xsl:value-of select="CUSTLOGINID"/></Email>
  </PrimaryContact>
</Customer>
</xsl:template>
</xsl:stylesheet>

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

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

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

<?xml version = '1.0' encoding = 'UTF-8'?>
<!DOCTYPE Customer SYSTEM "http://xmlville.com/customer.dtd">
<Customer id="123456789123456" Name="Huntington services Company">
   <PrimaryContact>
      <Name>
         <Given>James</Given>
         <Surname>Bond</Surname>
      </Name>
      <Email>jamesb</Email>
   </PrimaryContact>
</Customer>

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
http://technet.oracle.com/tech/java
http://technet.oracle.com/tech/xml
----- Original Message -----
From: Abhishek Srivastava <abisheks at india.hp.com>
To: xml dev mailing list <xml-dev at ic.ac.uk>
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
database.

<ROWSET>
 <ROW num="1">
  <SPNAME>Huntington services Company             </SPNAME>
  <CUSTPERMID>123456789123456     </CUSTPERMID>
  <CUSTLOGINID>jamesb              </CUSTLOGINID>
  <FIRSTNAME>james </FIRSTNAME>
  <LASTNAME>Bond </LASTNAME>
 </ROW>
 </ROW>
</ROWSET>

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

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

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 ?

Thanks,
Abhishek.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    _/               Abhishek Srivastava
   _/                Hewlett Packard ISO
  _/_/_/   _/_/_/    -------------------
 _/   /   _/  _/     (Work)   +91-80-2251554 x1190
_/  _/   _/_/_/      (Ip)     15.10.47.37
        _/           (Url)    http://sites.netscape.net/abhishes/homepage
       _/            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 ic.ac.uk
Archived as: http://www.lists.ic.ac.uk/hypermail/xml-dev/ and on CD-ROM/ISBN 981-02-3594-1
To unsubscribe, mailto:majordomo at ic.ac.uk the following message;
unsubscribe xml-dev
To subscribe to the digests, mailto:majordomo at ic.ac.uk the following message;
subscribe xml-dev-digest
List coordinator, Henry Rzepa (mailto:rzepa at ic.ac.uk)