SQL/XML in JDBC Applications
The simple way for Java applications to generate XML from SQL queries using the SQL/XML features of SQL 2003

The "Customers" table contains a customer identifier, customer name, and customer address. The "Projects" table contains a project identifier, project name, and customer identifier.

Here's the desired XML output for a single customer:

<customer id="4">
    <name>Hardware Heaven</name><city>Washington</city>
    <projects>
      <project id="2"><name>Pegasus</name></project>
      <project id="8"><name>Typhoon</name></project>
    </projects>
  </customer>

SQL/XML Example

The solution to our problem of generating XML structures from relational data is straightforward using SQL/XML. In the following solution, we use the SQL/XML XMLAGG function to build the list of projects associated with a given customer:

SELECT
   c.CustId,
   XMLELEMENT(NAME "customer",
      XMLATTRIBUTES(c.CustId AS "id"),
      XMLELEMENT(NAME "name",c.Name),
      XMLELEMENT(NAME "city",c.City),
      XMLELEMENT(NAME "projects",
      (SELECT XMLAGG(XMLELEMENT(NAME "project",
                 XMLATTRIBUTES(p.ProjId AS "id"),
                 XMLELEMENT(NAME "name",p.Name)))
      FROM Projects p 
      WHERE p.CustId=c.CustId))) AS "customer-projects"
FROM Customers c

The XML values are returned in the columns of a normal SQL result set as shown:

Using JDBC and Connect for SQL/XML, we can extract these XML values programmatically from the result set to construct an XML document as shown in the following code:

// Create the Connect for SQL/XML JDBC connection
connectWithConnectForSQLXML();

// Build SQL/XML query
StringBuffer sqlXml = new StringBuffer();
sqlXml.append ( " SELECT "); 
sqlXml.append ( " c.CustId, ");
sqlXml.append ( " XMLELEMENT (NAME \"customer\", ");
sqlXml.append ( " XMLATTRIBUTES(c.CustId AS \"id\"), ");
sqlXml.append ( " XMLELEMENT(NAME \"name\", c.Name), ");
sqlXml.append ( " XMLELEMENT(NAME \"city\", c.City)), ");
sqlXml.append ( " XMLELEMENT(NAME \"projects\", " );
sqlXml.append ( " (SELECT XMLAGG(XMLELEMENT(NAME \"project\", ");
sqlXml.append ( "            XMLATTRIBUTES(p.ProjId AS \"id\"), ");
sqlXml.append ( "            XMLELEMENT(NAME \"name\",p.Name))) ");
sqlXml.append ( " FROM Projects p ");
 
sqlXml.append ( " WHERE p.CustId=c.CustId))) AS \"customer-projects" ");
sqlXml.append ( " FROM Customers c ");

// Output result header
System.out.println( "---------------------------------------" );
System.out.println( "Query result (via getString on XMLType)" );
System.out.println( "---------------------------------------" );

// Execute the SQL/XML query
resultSet = stmt.executeQuery(sqlXml.toString());

// Retrieve data from the resultset
while ( resultSet.next() )
   {
   XMLType xmlType = (XMLType)resultSet.getObject(1);
   System.out.println(xmlType.getString());
   }

resultSet.close();

The SQL/XML query that produces the desired result can be easily maintained outside the application within a properties file or database, for example. Because SQL/XML is standards-based, this same query will work across a variety of databases.

Although the SQL/XML standard currently supports queries and not updates, DataDirect Connect for SQL/XML provides proprietary extensions to the SQL99 Insert, Update, and Delete statements that allow you to update data stored in relational databases with information extracted from an XML document. You specify XPath expressions to extract the relevant subset of information from the XML document. For example, let's assume that a new project named Gryphon is added for the customer Hardware Heaven and the resulting XML document looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<customers>
    <customer id="4">
    <name>Hardware Heaven</name><city>Washington</city>
    <projects>
      <project id="2"><name>Pegasus</name></project>
      <project id="8"><name>Typhoon</name></project>
    <project id="11"><name>Gryphon</name></project>
    </projects>
  </customer>
</customers>

To update the Projects database in our example with the new project named Gryphon, we can insert a new row in the Projects table using the proprietary extension for the SQL99 Insert statement provided by Connect for SQL/XML. Our code would look like this:

INSERT xml_document('c:/temp/newprojects.xml')
   into Projects (ProjId, Name, CustId)
   xml_row_pattern('/customers/customer/projects/project')
   values(xml_xpath('@id','Integer'),
          xml_xpath('@name/text()'),
          xml_xpath('../../@id','Integer')
          )

For more information about XPath, refer to http://www.w3.org/TR/xpath.

Custom-Coding

Custom-coding a solution to solve our problem is an alternative approach. APIs that can be used to access the database (for example, JDBC) have been available for some time. More recently, XML APIs (primarily, DOM and SAX) have been added to the developer's arsenal of available tools. Why not custom-code a solution that directly accesses the database and passes the information to one of the XML APIs?

Two issues exist with this approach. The first issue is maintainability. If you examine the custom-coded example that follows, you can see that a substantial amount of code is used to implement a simple conversion. Business requirements tend to change over time, so it is realistic to assume that the requirements for the document structure used to create the XML will probably change. With all this code, adding a new element from another RDBMS column to the document structure, for example, can be time-consuming.

The second issue is application performance. Typically, the queries executed on the database correspond to the structure of the XML document being created. The first query selects top-level elements, and for each nested structure, a new database query is used. This is not the fastest approach available, particularly for heavily nested documents [1].

Here's a code excerpt that uses JDBC to retrieve the data to be converted from the database. Code is written to retrieve data from each relational table, then, additional code is devoted to joining the data to create a usable XML document.

// Open JDBC Connection
String url="jdbc:datadirect:sqlserver://localhost:1433;databaseName=JXTR";
Class.forName("com.ddtek.jdbc.sqlserver.SQLServerDriver");
con=DriverManager.getConnection(url,"demo","secret");

// Create XML document
DocumentBuilderFactory dbf=DocumentBuilderFactory.newInstance();
DocumentBuilder db=dbf.newDocumentBuilder();
doc=db.newDocument();

// Create the root element
Element root=doc.createElement("customers");
doc.appendChild(root);

// Create statement and result set for customer information
Statement stmt1=con.createStatement();
ResultSet rs1=stmt1.executeQuery(
  "select c.CustId,c.Name,c.City from Customers c");

// For all selected customers
while(rs1.next()) {
  // Create customer container element
  Element customer=doc.createElement("customer");
  root.appendChild(customer);
  // "id" attribute and retrieve contents from result set
  String custId=rs1.getString(1); 
  customer.setAttribute("id",custId);
  // "name" child element and retrieve contents from result set
  Element custName=doc.createElement("name");
  custName.appendChild(doc.createTextNode(rs1.getString(2)));
  customer.appendChild(custName);
  // "city" child element and retrieve contents from result set 
  Element custAddress=doc.createElement("city");
  custAddress.appendChild(doc.createTextNode(rs1.getString(3)));
  customer.appendChild(custAddress);
  // "projects" child container element
  Element projects=doc.createElement("projects");
  customer.appendChild(projects);

  // Create statement/result set for project information
  Statement stmt2=con.createStatement();
  ResultSet rs2=stmt2.executeQuery(
    "select p.ProjId, p.Name from Projects p where p.CustId="+custId);

// For all selected projects
  while(rs2.next()) {
    // Create project container element
    Element project=doc.createElement("project");
    projects.appendChild(project);
    // "id" attribute and retrieve contents from result set
    project.setAttribute("id", rs2.getString(1));
    // "name" child element and retrieve contents from result set
    Element projName=doc.createElement("name");
    projName.appendChild(doc.createTextNode(rs2.getString(2)));
    project.appendChild(projName);
    }
  }

// 'Print' DOM document (Xerces version)
OutputFormat of=new OutputFormat();
of.setIndent(2);
Writer ow=(Writer)new OutputStreamWriter(System.out);

XMLSerializer xmlS=new XMLSerializer(ow,of);
xmlS.serialize(doc);

DB2 UDB

For some time, DB2 has been shipping an XML integration tool, DB2 XML Extender [2]. With version 8.1, DB2 also supports SQL/XML [3].

DB2 XML Extender, like all DB2 extenders, works as user-defined data types (UDTs) and associated user-defined functions (UDFs) that implement operations on these UDTs. DB2 XML Extender defines three UDTs: XMLVarchar, XMLCLOB, and XMLFILE. These data types are used to store XML information in its native format, and the operations defined on these UDTs manipulate the XML information.

DB2 XML Extender supports two major modes of operation: XML column mode and XML collection mode.

XML Column Mode

In XML column mode, complete XML documents are stored in a single database column (using one of the previously mentioned XML UDTs). Furthermore, parts of the XML document can be stored in normal RDBMS (indexed) table columns that are linked to the original XML document for quick lookup. Although this is certainly a useful feature, it does not solve the problem of moving data to and from the relational data model and the XML data model.

XML Collection Mode

XML collection mode supports both composing and writing XML information from or into DB2 table columns. The XML collection mode has the following characteristics:

• A Document Access Definition file (DAD) describes the mapping between the data persisted in table columns and information stored in XML elements or attributes.
  
• A DAD file is an XML document that supports two different mapping schemes: RDB_node and SQL mapping. SQL mapping-based DAD files can only be used to compose XML documents (not to de-compose them).
  
• When using RDB_node mapping, references to one or more tables and columns are inserted in the DAD file to specify which table columns map to which XML element or attribute.
  
• When using SQL mapping, a single SQL SELECT statement is used to define the content of the XML document. The column names returned from the SELECT statement are used to specify the link to the XML information (elements/attributes).
  
• DAD files can be passed as "string" objects to the DB2 XML Extender UDFs. Alternatively, DB2 XML Extender allows you store DAD files in a catalog in the database and reference them by name.
  
• Composed XML is inserted in a database table using an available XML UDT. To retrieve the XML in your application, you use a normal SELECT statement.
  

The following contents of a DAD file implements the mapping for our example. Looking at the contents of this DAD file, you can see that it describes the structure of the XML document to create or consume the XML using element_node and attribute_node elements and that the mapping between the structure and the relational information is provided by using the RDB_node elements.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE DAD SYSTEM "dad.dtd">
<DAD>
  <validation>YES</validation>
  <Xcollection>
    <prolog>?xml version="1.0"?</prolog>
    <doctype>!DOCTYPE customers SYSTEM "customers.dtd"</doctype>
    <root_node>
      <element_node name="customers">
        <element_node name="customer">
          <RDB_node>
            <table name="Customers" key="CustId" />
            <table name="Projects" key="ProjId" />
            <condition>
              Customers.CustId=Projects.CustId
            </condition>
            </RDB_node>
          <attribute_node name="id">
            <RDB_node>
              <table name="Customers" />
              <column name="CustId" type="integer"/>
            </RDB_node>
           <RDB_node>
              <table name="Projects" />
              <column name="CustId" type="integer"/>
            </RDB_node>

          </attribute_node>
          <element_node name="name" >
            <text_node>
              <RDB_node>
                <table name="Customers" />
                <column name="Name" type="varchar(50)"/>
              </RDB_node>
            </text_node>
          </element_node>
          <element_node name="city" >
            <text_node>
              <RDB_node>
                <table name="Customers" />
                <column name=“city” type="varchar(50)"/>
              </RDB_node>
            </text_node>
          </element_node>
          <element_node name="projects">
            <element_node name="project">
              <attribute_node name="id">
                <RDB_node>
                  <table name="Projects" />
                  <column name="ProjId" type="integer"/>
                </RDB_node>
              </attribute_node>
              <element_node name="name" >
                <text_node>
                  <RDB_node>
                    <table name="Projects" />
                    <column name="Name" type="varchar(50)"/>
                  </RDB_node>
                </text_node>
              </element_node>
            </element_node>
          </element_node>
        </element_node>
      </element_node>
    </root_node>
  </Xcollection>
</DAD>

The DAD file can be used to either retrieve the relational data from the database into XML or insert the XML into the database using the stored procedures ddGenXML (to retrieve the relational data and convert it to XML) and dxxShredXML (to insert the XML into the database).

Oracle

Similar to DB2 UDB, Oracle has been shipping XML integration tools for some time. Prior to Oracle 9iR2, most of these integration tools were bundled in a separately downloadable package (Oracle XDK). With release 9iR2, Oracle positions its database server also as an XML database server. The Oracle 9iR2 release adds a number of XML-related features, including SQL/XML support.

From the complete set of XML related modules and features available in Oracle 9iR2, XML SQL Utility (XSU) [4] and SQL/XML [5] support only are relevant for the information mapping problem we are addressing in this document.

XML SQL Utility

XSU is packaged as a set of Java classes. A wrapper PL/SQL package (DBMS_XMLGEN) and a flexible command-line utility are provided as well.

From Relational to XML

XSU can render the result of any SQL query into a fixed-format XML structure. The mapping from query result to XML format takes into account the features of the Oracle (object) type system and, in combination with object views defined on top of non-object typed data, a flexible mapping can be created.

The rules that determine the XML structure derived from the query result include:

• By default, a <ROWSET> container element is created. Optionally, the element name can be changed or the element can be omitted.
  
• For each row retrieved, a <ROW> container element is created with an ID attribute that contains an increasing cardinal number. Optionally, the element name can be changed; the ID attribute or the complete <ROW> element can be omitted.
  
• For each column in the retrieved rows, a sub-element is created. Its name is derived from the result set column name. Its content is the value that is retrieved from the database.
  
  • When the name derived from the query result starts with a ‘@’, an attribute is created instead of an element.
    
  • When the retrieved data is an instance of an Oracle object type, a container element is created. Its name is derived from the column name. For each attribute of the object type, an element is created. Its name is derived from the object attribute name. The content is set to the attribute value retrieved from the database.
    
  • When the retrieved column data is an instance of an Oracle collection type, a container element for the collection is created. Its name is derived from the column name. For each item in the collection, a container element is added. Its name is derived from the column name and extended with an _ITEM suffix. Finally, beneath this container element, elements are created with the actual data. The names of these elements are derived from the names of the attributes of the collection type.
    

From XML to Relational

The inverse operation (inserting, updating, or deleting relational data based on XML information), is slightly less flexible. For example:

• Attributes are ignored.
  
• To create the requested mapping, you often must create object views on top of more traditional relational data. These views are not always updateable. A work-around based on database triggers is possible, but clearly is less elegant.
  

Using XSU

A set of Java classes (and a PL/SQL package) is provided to execute XSU mappings. A powerful command-line tool exposes most of the options in the API. Finally, the XSQL Servlet is provided to support scalable deployment in Servlet container engines.

XSU Java Classes

The OracleXMLQuery class can be used to execute a query and retrieve the resulting XML. Different methods are available to perform the following tasks:

• Tune the resulting XML
  
• Retrieve the XML in different formats (String, DOM, SAX)
  
• Scroll through the XML result
  
• Create a DTD or XML Schema based on the query structure
  

The OracleXMLSave class can be used to insert, update, or delete relational information. The following features are exposed:

• Set batch size for batched JDBC calls and commit batch size
  
• Define a key column list (update and delete)
  
• Restrict columns to update
  
• Execute the insert, update, or delete statement
  

XSU Example

The following code excerpt shows the use of the XSU classes. The code extracts the Customer and Project information from the database, and creates the XML document shown in our example.

// Open JDBC Connection
String url="jdbc:oracle:thin:@localhost:1521:ORA92";
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection(url,"jxtr","jxtr" );

// Query
StringBuffer sb=new StringBuffer();
sb.append("SELECT ");
sb.append("  c.custid as \"@id\", ");
sb.append("  c.name, ");
sb.append("  c.City, ");
sb.append("  cursor (  ");
sb.append("    SELECT ");
sb.append("      p.projid as \"@id\", ");
sb.append("      p.name ");
sb.append("    FROM Projects p ");
sb.append("    WHERE p.CustId=c.CustId ) AS projects ");
sb.append(" FROM Customers c ");

// XSU object
OracleXMLQuery q=new OracleXMLQuery(con,sb.toString());

// Result "customization"
q.setRowsetTag("customers");
q.setRowTag("customer");
q.useLowerCaseTagNames();
q.setRowIdAttrName("");

// Stylesheet for result "clean-up"
q.setXSLT("q.xslt",null);

// Generate result document and print
Document resDoc=q.getXMLDOM();
XMLDocument oraDoc=(XMLDocument)resDoc;
oraDoc.print(System.out);

NOTES:

• The cursor function is used to nest a SELECT statement in the Select list of the parent query.
  
• The OracleXMLQuery.setXSLT method was invoked because the XML document created by the default XSU mapping did not match the intended result exactly. XSU does offer flexible XSLT integration. In this specific example, a simple XSLT script (not shown) was needed to create the final XML document.
  

Microsoft SQL Server

SQL Server 2000, and later, released SQL/XML and Web Services toolkits that expose a series of features that allow you to publish relational data as XML, or populate or update relational information with data retrieved from an XML document. The following sections provide an overview of these features.

Transact-SQL Extensions [6]

• FOR XML SELECT statement extension - The SELECT syntax is extended with a FOR XML clause that indicates the result of the query must be returned as XML. Different options for returning the XML are available: raw, auto, and explicit. The ease of use of these options range from easy with limited XML mapping ability (FOR XML RAW) to complex with powerful XML mapping features (FOR XML EXPLICIT).
  

The syntax for the FOR XML extension is:

FOR XML 
  [RAW|AUTO|EXPLICIT]
  [, XMLDATA] 
  [, ELEMENTS]
  [, BINARY BASE64]

For details about the different options and parameters, refer to the SQL Server Transact SQL documentation [6].

• OPENXML statement - OPENXML exposes an XML document as a rowset. The OPENXML row pattern (an XPath expression) defines a set of XML
  

sub-trees that become the individual rows in the rowset. Individual column values are selected from the XML sub-trees (rows) by specifying one or more column patterns using XPath. Because the result of an OPENXML statement is a rowset, its result can be used as the source for insert, update, or delete statements.

The syntax for the OPENXML extension is:

OPENXML (
  idoc int [in], 
  rowpattern nvarchar[in],
  [flags byte[in]] ) 
[WITH(SchemaDeclaration|TableName)]

NOTE: The idoc parameter contains a handle to a parsed XML document. This handle is created from an XML document by invoking the sp_xml_preparedocument stored procedure.

For details about the different options and parameters, refer to the SQL Server Transact SQL documentation [6].

Web Services Toolkit [7]

• Client-side XML formatting – ADO and ADO.NET providers are available that support client-side XML formatting for query results. These providers also support the FOR XML server-side XML formatting described previously.
  
• XML views – XML Schema files contain annotations that map the XML schema to one or multiple RDBMS tables and associated columns. Once an XML view is defined, XPath expressions can be evaluated against these XML views.
  
• DiffGram –XML documents with a fixed schema. The information contained in the XML file lists before and after values that register the changes that need to be applied to an XML view of the RDBMS tables/columns.
  
• Templates – Template XML files that contain one or multiple XPath expressions against one or multiple XML views. These template files can also contain SELECT FOR XML statements. Executing the templates combines the result of all these expressions in one XML document.
  

NOTE: The set of SQL Server features Microsoft identifies as SQLXML is not based on the SQL/XML standard.

Example

The FOR XML extension can be used to create the XML document from the Customer and Project table, and OPENXML can be used to insert the information from the XML document in the database tables.

NOTE: XML views and DiffGram support similar functionality, but they are not easily accessible from within a Java program. The following example is a Java code excerpt that shows how to use the OPENXML function to update columns in the Customers and Projects table with information extracted from the example XML document.

... 
// Build Transact-SQL statement
StringBuffer sb=new StringBuffer();

// Variable declarations
sb.append("DECLARE @idoc int\n");
sb.append("DECLARE @doc varchar(3000)\n");

// Define XML document parameter and parse
sb.append("SET @doc =?\n");
sb.append("EXEC sp_xml_preparedocument @idoc OUTPUT, @doc\n");

// Update Customers from XML
sb.append("update Customers\n");
sb.append("  set Customers.Name=u.Name, Customers.City=u.City\n");
sb.append("FROM OPENXML (@idoc, '/customers/customer',2) \n");
sb.append("WITH  (CustId integer '@id', Name varchar(50) 'name/text()',\n");
sb.append("       Address varchar(50) 'address/text()') u\n");
sb.append("where Customers.CustId=u.CustId\n");

// Update Projects from XML
sb.append("update Projects\n");
sb.append("  set Projects.Name=u.Name\n");
sb.append("FROM OPENXML (@idoc, '/customers/customer/projects/project',2) \n");
sb.append("WITH  (CustId integer '../../@id', ProjId integer '@id',\n ");
sb.append("       Name varchar(50) 'name/text()' ) u\n");
sb.append("where Projects.ProjId=u.ProjId\n");

// Release 'parsed' XML document
sb.append("EXEC sp_xml_removedocument @idoc\n");

// Create statement and set bind marker value
PreparedStatement stmt=con.prepareStatement(sb.toString());
stmt.setString(1,readFile("example.xml"));

// Execute
stmt.execute();
int rowCount;

// Loop through results
do
  {
  rowCount=stmt.getUpdateCount();
  if(rowCount >= 0)
    {
    System.out.println("Updated:"+rowCount);
    stmt.getMoreResults();
  }
  }
while( rowCount>=0 );

con.close();

The following code excerpt creates a Transact SQL statement that is executed using a PreparedStatement.execute method invocation. The Transact SQL requires only one input parameter, the XML document. The value for this input parameter is provided by invoking PreparedStatement.setString.

The Transact SQL statement contains two OPENXML statements. The first extracts Customer information from the input XML document and uses this data to update the Name and Address columns in the Customers table. The second OPENXML statement extracts information from the input XML document and uses this data to update the Name column in the Projects table.

The inverse mapping, creating the XML document based on the RDBMS table data, can be achieved with the following Transact SQL FOR XML statement.

SELECT 
   1 AS Tag,
   0 AS Parent,
   NULL AS [customers!1], 
   NULL AS [jxtr_order_q1!15!!hide], 
   NULL  AS [customer!2!id], 
   NULL  AS [customer!2!name], 
   NULL  AS [projects!3], 
   NULL AS [jxtr_order_q2!16!!hide], 
   NULL  AS [project!4!id], 
   NULL  AS [project!4!name]  
UNION ALL 
   SELECT 2,1, NULL ,1,c.CustId,c.Name, NULL , NULL , NULL , NULL    
   from  Customers   c    
UNION ALL 
   SELECT 3,2, NULL ,2,c.CustId,c.Name, NULL , NULL , NULL , NULL    
   from  Customers   c    
UNION ALL 
   SELECT 4,3, NULL ,3,c.CustId,c.Name, NULL ,1,p.ProjId,p.Name   
   from  Customers   c , Projects   p    
   WHERE  ( p.CustId  =  c.CustId )   
ORDER BY 5,6,7,4,9,10,8,Tag 
FOR XML EXPLICIT