RE: [xml-dev] Out of memory error with XML and Java ResultSet

[Jonathan Robie <jonathan.robie@datadirect.com>]

At 04:46 PM 9/8/2003, Brown, Jason B. wrote:
>Jonathan,
>
>I am creating an XML hierarchy as the ultimate result.  I am using the
>data contained in the result set to produce an XML hierarchy, which I
>have defined.

You may want to consider using either XQuery or SQL/XML to create the XML 
you need directly as a result of your query. (Shameless Plug Alert: I am an 
editor of XQuery, and a Program Manager for both an XQuery product and a 
SQL/XML product).

Unfortunately, the standard Java API for XQuery, XQJ (JSR 225) is in early 
stages of design, so if you need a standard cross-platform solution, you 
probably want to wait for that API to be developed. If a proprietary API is 
OK, and XQuery is available in your environment, it might solve your 
problem, provided that API allows you to return results incrementally, 
perhaps as a SAX stream.

SQL/XML uses the JDBC API. You could use SQL/XML to create the XML 
hierarchies directly, as part of your SQL query. SQL/XML is a part of SQL 
2003, and provides a set of XML publishing functions that allow you to 
create XML directly in your SQL query. For instance, here's a query in SQL/XML:

SELECT XMLELEMENT(NAME "customer",
          XMLATTRIBUTES(c.CustId AS "id"),
          XMLELEMENT(NAME "name",c.Name),
          XMLELEMENT(NAME "city",c.City)) AS "CustInfo"
FROM Customers c

This is a simple query, but SQL/XML is part of the SQL standard, and you 
can do whatever SQL functionality you need, and the constructor functions 
can create whatever XML structures you need. In the following query, sqlXml 
is the name of a string buffer which contains the above query. This code 
executes the query and returns the results as text:

resultSet = stmt.executeQuery(sqlXml.toString());

while ( resultSet.next() )
{
     XMLType xmlType = (XMLType)resultSet.getObject(1);
     System.out.println(xmlType.getString());
}

In this code, the XMLType is the one thing you need that is not defined by 
the JDBC or SQL/XML standards. It is anticipated that an XML type will be 
added to JDBC 4.0. This example uses an XMLType implementation from our own 
cross-platform SQL/XML implementation.

This may solve your memory problem, since you can let the query create the 
structures you need, doing whatever correlation is needed to build your 
structures, and you can return the results a little at a time. Obviously, 
you may need to create some wrapper tags outside of it. Incidentally, you 
can also get the XML results back as a DOM tree, a JDOM tree, or as a SAX 
stream. I used text in the above example because it is fast and lean.

You will be able to do the same with XQuery with a proprietary API if you 
have a good implementation in your environment, and the XQJ API will be 
able to do the same kinds of things, it just isn't available yet.

Incidentally, here is a white paper that compares SQL/XML to the XML 
extensions from vendors and to straight JDBC + DOM programming:

http://www.datadirect.com/products/connectsqlxml/docs/sqlxml_whitep.pdf

Hope this is helpful,

Jonathan