2 Understanding Connect for SQL/XML

This chapter describes some key features of Connect for SQL/XML and provides examples to help you decide how to use them.

Using the Java Packages

Connect for SQL/XML contains two Java packages: com.ddtek.jdbc.jxtr and com.ddtek.jxtr. The com.ddtek.jdbc.jxtr package is mainly used for processing SQL/XML queries, and the com.ddtek.jxtr package is used for processing jXTransformer queries and jXTransformer write statements.

One interface, JXTRStatementFactory, within the com.ddtek.jdbc.jxtr package is used to create instances of com.ddtek.jxtr objects from a SQL/XML JDBC driver connection object.

Using Both SQL/XML and jXTransformer Statements in a Java Application

Both SQL/XML queries and jXTransformer statements can be used in the same Java application. In this case, you can either:

When you are writing an application that uses both SQL/XML queries and jXTransformer statements, you may find it useful to use this interlace so that you only have one database connection in your application.

Using SQL/XML Queries

This section provides information to help you use SQL/XML queries to transform relational data into XML.

Using key_expr Hints

You can optimize processing of a SQL/XML query by specifying key_expr hints as comments in your SQL/XML query. A key_expr hint uniquely identifies each of the rows retrieved from the database. If you do not specify any key_expr hint, all the selected database columns concatenated together compose the key. Note that the key columns are used to link parent rows to child rows when you are using nested queries. In SQL/XML queries that do not contain nested queries, there is no need to specify key_expr hints. Notice that only the sorted_outer_union algorithm uses keys; see the next section for a definition of this algorithm.

Using rewrite_algorithm Hints

A SQL/XML query cannot be passed directly to the underlying database system; therefore, the SQL/XML JDBC driver translates the query to one or multiple SQL Select statements. The SQL/XML JDBC driver uses one of the following rewrite algorithms to perform this translation:

You can specify which algorithm to use by specifying a rewrite_algorithm hint in your SQL/XML query. Hints are placed within comments in the SQL/XML query. If no algorithm is specified, the driver uses sorted outer union.

Creating Result Sets From SQL Queries

In addition to creating result sets from SQL/XML queries, the SQL/XML JDBC driver also allows you to generate result sets from normal SQL queries. These result sets do not contain XML values unless the values are stored in the database as XML data types. For example, if you process the following SQL query using the SQL/XML JDBC driver:


SELECT e.FirstName, e.LastName  

FROM Employees e AS Employees 

WHERE e.EmpId < 3

Employees
Marc	Marshall
Brian	Ayers

This process is no different than returning a result set from a SQL query with any DataDirect Technologies JDBC driver. See "Connecting to the Database" for instructions on connecting with the SQL/XML JDBC driver.

Using jXTransformer Queries

This section provides information to help you use jXTransformer queries to transform relational data into XML.

Creating Hierarchical XML Documents

Using the jXTransformer query syntax, you can create hierarchical XML documents and define parent and child relationships for data from one or multiple database tables. Also, you can use nested jXTransformer queries to link data from multiple database tables and hierarchically structure that data.

Let us look at a simple jXTransformer query that retrieves data from one database table and creates a hierarchical XML document with one parent XML element named Employees_info, and with one child element named name that is parent to two child elements named first and last.


SELECT 

xml_element('Employees_Info', 

    xml_attribute('Department', e.Dept), 

    xml_element('name', 

          xml_element('first', e.FirstName), 

          xml_element('last', e.LastName) ) ) 

FROM Employees e WHERE e.Dept = 'QA'


<Employees_Info Department='QA'> 

   <name> 

      <first>Paul</first> 

      <last>Steward</last> 

   </name> 

</Employees_Info> 

<Employees_Info Department='QA'> 

   <name> 

      <first>John</first> 

      <last>Jenkins</last> 

   </name> 

</Employees_Info>

Now, look at another query that uses nested jXTransformer queries. In this example, the nested query uses the e.EmpId column to link the rows selected in the parent query with the rows selected in the nested, or child, query. Using xml_attribute_key in the parent query creates an XML attribute and specifies that the e.EmpId column in the database uniquely identifies the rows retrieved from the first query. Also, it specifies that the attribute value uniquely defines the parent element named employee.


SELECT 

  xml_element('employee', 

    xml_attribute_key('ID', e.EmpId), 

    xml_attribute('name', 

   {fn concat({fn concat(e.FirstName, ' ')},e.LastName)}), 

    (SELECT 

      xml_element('project', 

        xml_attribute('name',p.Name), 

        xml_attribute('task',a.Task)) 

    FROM Projects p, Assignments a

    WHERE p.ProjId=a.ProjId and a.EmpId=e.EmpId)) 

FROM Employees e WHERE e.EmpId < 3


<employee ID='1' name='Marc Marshall' 

  <project name='Medusa' task='Analysis'></project> 

    <project name='Medusa' task='Documentation'></project> 

    <project name='Medusa' task='Planning'></project> 

    <project name='Medusa' task='Testing'></project> 

    <project name='Phoenix' task='Analysis'></project> 

    <project name='Phoenix' task='Documentation'></project> 

</employee> 

<employee ID='2' name='Brian Ayers' 

  <project name='Python' task='Analysis'></project> 

    <project name='Python' task='Development'></project> 

    <project name='Hydra' task='Analysis'></project> 

    <project name='Hydra' task='Documentation'></project> 

</employee>

NOTE: When you use nested queries, you must use an explicit key, specified through one, or a combination, of xml_attribute_key, xml_element_key, and xml_hide_key; otherwise, Connect for SQL/XML creates an implicit key by making all the columns specified in the top-level jXTransformer query part of the key, in which case, the query results could be incorrect.

Using Keys

You can optimize processing of a jXTransformer query by specifying key columns using the "key" keyword. If you do not specify any key columns, Connect for SQL/XML automatically assumes that all the selected columns concatenated together compose the key. Notice that the key columns are used to link parent rows to child rows when you are using jXTransformer nested queries. In jXTransformer queries that do not contain nested queries, there is no need to specify key columns.

Look at a jXTransformer query that uses the jXTransformer constructor xml_hide_key. In this example, xml_hide_key is used to relate parent rows to child rows.


SELECT 

  xml_element('employee', 

    xml_hide_key(e.EmpId), 

    xml_attribute('name', 

   {fn concat({fn concat(e.FirstName, ' ')},e.LastName)}), 

    (SELECT 

      xml_element('project', 

        xml_attribute('name',p.Name), 

        xml_attribute('task',a.Task)) 

    FROM Projects p, Assignments a 

    WHERE p.ProjId=a.ProjId and a.EmpId=e.EmpId)) 

FROM Employees e WHERE e.EmpId < 3


<employee name='Marc Marshall'> 

    <project name='Medusa' task='Analysis'></project> 

    <project name='Medusa' task='Documentation'></project> 

    <project name='Medusa' task='Planning'></project> 

    <project name='Medusa' task='Testing'></project> 

    <project name='Phoenix' task='Analysis'></project> 

    <project name='Phoenix' task='Documentation'></project> 

</employee> 

<employee name='Brian Ayers'> 

    <project name='Python' task='Analysis'></project> 

    <project name='Python' task='Development'></project> 

    <project name='Hydra' task='Analysis'></project> 

    <project name='Hydra' task='Documentation'></project> 

</employee>

Hiding Information

The jXTransformer query xml_hide[_key] constructor allows you to select and "hide" database columns you do not want to display in the resulting XML document. For example, suppose you have a database table that contains the following employee information:

SSN (primary key)
LastName
FirstName
JobTitle
Department
Salary
HireDate
ParentSSN

From this database table, you want to retrieve the names and salaries of the employees in the "QA" department as well as list dependents for those employees. In this case, to optimize processing, you want to write a jXTransformer query that selects the SSN column (a primary key), but you do not want to display the SSN information in the XML document, so you use the xml_hide_key construct to select the SSN data. The jXTransformer query might look like:


SELECT 

  xml_element('QAemployees', 

    xml_hide_key(e.SSN), 

    xml_element('Salaries', 

      xml_element('first', e.FirstName), 

      xml_element('last', e.LastName), 

      xml_element('salary', e.Salary) ) 

      (SELECT 

       xml_element('children', 

          xml_attribute('first-name', c.first), 

          xml_attribute('last-name', c.last) ) 

       FROM Employees C WHERE c.ParentSSN=e.SSN) ) 

FROM Employees e WHERE e.Department='QA'

This query retrieves information about employees in the QA department and lists the names of their dependents. In this query, using xml_hide_key optimizes processing of the query by linking information about the parents with information about their children through a common denominator, the SSN information.

Creating ID/IDREFS Links

The jXTransformer query syntax allows you to create IDREFS as values for XML attributes. IDREFS are references to unique attribute ID values for XML elements in the same XML document.

You can use the jXTransformer query constructor xml_attribute to create IDREFS using the following syntax:

When you specify a SQL query, a space-separated concatenation of the complete result set is created; these values typically are IDREFS for an ID attribute of another XML element in the same XML document.

Using the xml_attribute query constructor, in combination with multiple top-level queries within a jXTransformer query, you can create ID/IDREFS links in the resulting XML document. To specify multiple top-level queries, separate the queries with a semicolon (;). The result is a single XML document that contains the concatenation of the results of the different top-level queries.


<employees emp-id="e-1" emp-name="Marshall" 

  emp-benefits="b-1 b-3" /> 

<employees emp-id="e-12" emp-name="Steward" 

  emp-benefits="b-3" /> 

<employees emp-id="e-2" emp-name="Allen" 

  emp-benefits="b-1 b-4" /> 

<benefits benefit-id="b-1" benefit-description="Bonus"

  benefit-employees="e-1 e-2" /> 

<benefits benefit-id="b-3" benefit-description="Car"

  benefit-employees="e-1 e-12" /> 

<benefits benefit-id="b-4" benefit-description="Commission"

  benefit-employees="e-2" />

Creating XML Document Fragments

The jXTransformer query syntax allows you to create complete XML documents and XML document fragments. Unlike a document fragment, a complete XML document contains an XML root element; in addition, it can contain processing instructions, comments, and a reference to a private or public external DTD.

Typically, you create XML document fragments to insert into an existing DOM or JDOM document. For example, suppose your company wants to produce an XML document that contains a sales report. This sales report contains a message to all sales representatives as well as sales data that is retrieved from a relational database. In this scenario, you first create a DOM document that contains the message. Then, you use a jXTransformer query to retrieve the sales data from the database and to place the XML results into the existing DOM document.


import java.io.OutputStreamWriter; 

import java.sql.*; 

import org.w3c.dom.*; 

import javax.xml.parsers.*; 

 

import com.ddtek.jxtr.*; 

{ 

//Load properties from the resource 

loadProperties(); 

 

//Create JDBC connection 

jdbcConnect(); 

 

//Build jXTransformer query 

StringBuffer jxtrQ = new StringBuffer(); 

jxtrQ.append ( "select "); 

jxtrQ.append ( "  xml_element('Order-by-SalesID', "); 

jxtrQ.append ( "     xml_attribute('Salesperson', o.SalesID), "); 

jxtrQ.append ( "     xml_attribute('CustomerID', o.CustomerID), "); 

jxtrQ.append ( "  xml_element('OrderDate', o.OrderDate), "); 

jxtrQ.append ( "  xml_element('ShipDate', o.ShipDate), "); 

jxtrQ.append ( "  xml_element('OrderAmount', o.OrderAmt) ) "); 

jxtrQ.append ( "from Orders o order by o.SalesID " ); 

 

//Construct new JXTRQuery object 

JXTRQuery jxtrQuery = JXTRQuery (conn, new String ( jxtrQ ) ) 

 

// Create DOM Document through JAXP 

DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance (); 

DocumentBuilder db = dbf.newDocumentBuilder (); 

Document doc = db.newDocument (); 

 

// Create root node and place it in DOM document 

domRoot = doc.createElementNS ( null, "sales-root" ); 

doc.appendChild ( domRoot ); 

 

// Create a child node for the root node and append it 

Node domS1 = doc.createElementNS ( null, "sales-query-result" ); 

domRoot.appendChild ( domS1 ); 

 

// Create text content for the subelement 

domS1.appendChild ( doc.createTextNode ( "Text message. . ." ) ); 

 

// Execute and place the result into the previously created DOM node 

jxtrQuery.executeDOM ( domS1 ); 

 

// Close JDBC connection 

jdbcDisconnect(); 

 

}

Creating XML in Result Sets

In addition to creating complete XML documents and XML document fragments, you can also return XML values in result sets from jXTransformer queries. For example, you may want to return a large XML result of a jXTransformer query as a result set so that you can process it row by row. To create a result set, use the executeQuery method in the JXTRQuery class. Once you have returned the XML as a result set, you can use the methods in the JXTRColInfo class to work with those XML results.

Generating DTDs and Schemas

You can generate a DTD or schema that describes the structure of the XML result of a jXTransformer query using the jXTransformer API or the DataDirect Query Builder for SQL/XML. For example, you may want to distribute a DTD or schema you have generated describing the XML results of a jXTransformer query to a business partner.

The JXTRQuery class implements the methods you can use to generate DTDs or schemas based on the XML result of a jXTransformer query.

See "Generating DTDs and XML Schemas" for instructions on generating DTDs or XML schemas using the DataDirect Query Builder for SQL/XML.

Choosing an XML Document Structure

jXTransformer queries provide the flexibility to define a complex hierarchical structure for XML documents. jXTransformer queries also allow you to generate XML documents from SQL queries. In this case, the rows and columns returned from the SQL queries are transformed into either to a flat-attribute or element-centric XML structure (see the following sections for definitions).

Hierarchical XML

For hierarchical XML structures, all data retrieved from the database is structured as defined by parent and child relationships, and nested queries, using the jXTransformer query syntax. The JXTRQuery class implements the methods to transform a jXTransformer query into an XML structured document. See "Creating Hierarchical XML Documents" for more information about structured XML.

Flat Attribute and Element-Centric Structures

The following XML documents are generated from executing a SQL query, rather than a jXTransformer query:

The JXTRResultSetWrapper class implements the methods required to execute a SQL query to generate attribute- and element-centric XML documents.

In the following example, data is retrieved from the FirstName and LastName table columns and is placed in an attribute-centric or element-centric XML document.


SELECT e.FirstName, e.LastName  

FROM Employees e 

WHERE e.EmpId < 3


<?xml version="1.0" encoding="UTF-8" ?> 

<jxtr-result> 

    <row FirstName='Marc' LastName='Marshall'/> 

    <row FirstName='Brian' LastName='Ayers'/> 

</jxtr-result>


<?xml version="1.0" encoding="UTF-8" ?> 

<jxtr-result> 

     <row> 

          <FirstName>Marc</FirstName> 

          <LastName>Marshall</LastName> 

     </row> 

     <row> 

          <FirstName>Brian</FirstName> 

          <LastName>Ayers</LastName> 

     </row> 

</jxtr-result>

Choosing an XML Output Format

The JXTRQuery and JXTRResultSetWrapper classes implement the methods listed in Table 2-1 for generating XML documents. These two classes are in the com.ddtek.jxtr package.

Table 2-1. jXTransformer Query XML Output Options
XML Document Output Options	Methods
Write the XML document as a character stream to a Writer object	executeWriter
Return the XML document as a DOM level 2 document object	executeDOM
Create the XML document under an existing DOM level 2 element node	executeDOM
Return the XML document as a JDOM tree	executeJDOM
Create the XML document under an existing JDOM tree element node	executeJDOM
Invoke SAX2 callbacks as XML document results are being retrieved from the database	executeSAX
Create the XML within a result set	executeQuery

Choosing a Rewrite Algorithm

A jXTransformer query cannot be passed to the underlying database system; therefore, Connect for SQL/XML translates a jXTransformer query to one or multiple SQL Select statements or uses one of the following jXTransformer rewrite algorithms to perform this translation:

For a complete description of each rewrite algorithm, refer to the Javadoc shipped with this product.

In most cases, you do not need to know the rewrite algorithm Connect for SQL/XML uses to translate a jXTransformer query; however, you may find it helpful to know the following information about the rewrite algorithms:

The JXTRQuery class contains methods that allow you to specify which rewrite algorithm you want to use for the translation of your jXTransformer query.

Using jXTransformer Write Statements

This section provides information you need to know to use jXTransformer write statements to write data from XML documents into your relational database.

jXTransformer Write Statement Processing Overview

When the jXTransformer API executes a jXTransformer write statement (Insert, Update, or Delete statements), the following events occur:

Disabling Autocommit Mode

When using jXTransformer write statements in Java applications, we recommend that Autocommit mode be disabled. Invoke con.setAutoCommit(false), execute the jXTransformer write statement, and then, invoke con.commit() or con.rollback().

Choosing an XML Input Format

The JXTRUpdate and JXTRSingleTableUpdate classes implement the methods listed in Table 2-2 for writing data from an XML document to a relational database.

Table 2-2. jXTransformer Write XML Document Options
XML Document Input Options	Methods
Read the XML document input as a character stream to a Reader object	setReader
Read the XML document input as a DOM level 2 document object	setDOM
Read the XML document input from a DOM level 2 element node	setDOM
Read the XML document input as a JDOM tree	setJDOM
Read the XML document input from a JDOM tree element node	setJDOM
Read the XML document input from a SAX2 input source	setSAX