9 Tutorial: Using SQL/XML Queries

---

This chapter contains a step-by-step tutorial that shows you how to create a SQL/XML query using the DataDirect Query Builder for SQL/XML (the Builder). It also shows you how to embed that query in a Java application and use Connect for SQL/XML hints to provide processing options that are not supported through the standard SQL/XML query syntax.

This tutorial steps you through the process of constructing the following SQL/XML query.

SELECT  
   XMLELEMENT (NAME "employee",  
      XMLATTRIBUTES(e.EmpId as "id"),  
      XMLELEMENT(NAME "names", 
         XMLELEMENT(NAME "first", e.FirstName),  
         XMLELEMENT(NAME "last", e.LastName)),  
      XMLELEMENT(NAME "hire-dates",  
         XMLATTRIBUTES(e.StartDate as  "start", 
         e.EndDate as "end")  
    )) as SQLXMLCOL1  
FROM Employees e WHERE e.EmpId = 12  

NOTE: The preceding SQL/XML query example can be found in the SQLXMLExample4.java file in the examples/src/examples/sqlxml directory in your DataDirect Connect for SQL/XML installation directory. For information on populating your database with test data and using this example, refer to the examples_readme.txt file in the examples directory.

Our SQL/XML query example generates a result set that looks like this:

SQLXMLCOL1
<employee id='12'> <names> <first>Paul</first> <last>Steward</last> </names> <hire-dates start='1997-01-04 00:00:00.0' end='NA'></hire-dates> </employee>

This tutorial uses this SQL/XML query example to demonstrate how to accomplish the following tasks:

• Create result sets that can contain XML.

• Use the following Connect for SQL/XML hints to specify processing options that are not available in the SQL/XML syntax:

• null_replacement is a hint that replaces NULL values retrieved from the database with a specified value. In our example, we replace any NULL value that is encountered with the letters "NA."

• timestamp_encoding is a hint that specifies the type of timestamp-to-string conversion to be used, either iso8601 or odbc, when timestamp information is retrieved from the database.

• key_expr is a hint that specifies a key or part of a multi-value key. Keys uniquely identify rows in the database selected by the base SQL query. In our example, the key is specified in the Builder and is translated into a key_expr hint.

See "Using Connect for SQL/XML Hints" for information about using Connect for SQL/XML hints.

Complete the tasks described in the next sections to create the SQL/XML query in our example. These tasks include:

• "Creating the Builder Project"

• "Constructing the SQL/XML Query in the Builder"

• "Using the SQL/XML Query in a Java Application"

Creating the Builder Project

Start the Builder. How you start the Builder depends on your platform:
On Windows: Run the builder.bat file located in the Connect for SQL/XML installation directory.
On UNIX: Run the builder.sh shell script located in the Connect for SQL/XML installation directory.

During a normal installation, the builder.bat file and builder.sh script are automatically customized to include the path to your JDK. If the installer was unable to detect a required JDK or you want to change the JDK to be used, refer to the DataDirect Connect for SQL/XML Installation Guide for instructions on configuring the startup file for the Builder.

Open a new Builder project for the SQL/XML query by selecting File / New Project. The New Project dialog box appears.





From the drop-down list, select SQL/XML.
Click OK. An untitled project node appears in the Tree view of the SQL/XML Statement window.





Save the Builder project by selecting File / Save Project As and specifying example4.cfbas the filename of the project. Notice that the project node is renamed to example4. As you work with this example, you can save your changes to the project file at any time by selecting File / Save Project.

Constructing the SQL/XML Query in the Builder

First, create a Base SQL Query for the SQL/XML query. The Base SQL Query is required. It helps define the data to be retrieved and facilitates the process of constructing the query. To create a Base SQL Query, right-click the project node, and select Insert / Base SQL Query Node. The Base SQL Query Node dialog box appears.





TIP: When specifying table names, you must use unique table aliases (for example, Employees e).

Type the following Base SQL Query for our SQL/XML query:

SELECT

e.EmpId,

e.FirstName,

e.LastName,

e.StartDate,

e.EndDate

FROM Employees e WHERE e.EmpId = 12

The Base SQL Query accomplishes the following tasks:

Selects columns in the query containing data to retrieve
Specifies the tables from which to retrieve data
Optionally, specifies filters on the Select statement

Notice that the Where clause in our example specifies a filter on the Select statement of the SQL query.

Click OK. The Builder checks the Base SQL Query for syntax. The Base SQL Query node appears in the project tree.





Now that you have defined which data to retrieve from the database for the SQL/XML query, you can define the XML structure, including the hierarchical relationships of the data.

Examine the SQL/XML query in our example. You need to create an XML element that contains employee information for an employee based on their employee ID, including first and last name, as well as the start and end hire dates for that employee.

SELECT

XMLELEMENT (NAME "employee",

XMLATTRIBUTES(e.EmpId as "id"),

XMLELEMENT(NAME "names",

XMLELEMENT(NAME "first", e.FirstName),

XMLELEMENT(NAME "last", e.LastName)),

XMLELEMENT(NAME "hire-dates",

XMLATTRIBUTES(e.StartDate as "start",
e.EndDate as "end")

)) as SQLXMLCOL1

FROM Employees e WHERE e.EmpId = 12

Notice that the element named employee in the SQL/XML query is a parent to the following attributes and elements:

An attribute named id that retrieves the employee ID (a key) from the database column e.EmpId in the database table Employees with an alias of e.
An element named names that contains two subelements that retrieve the first and last names of the employee from the database columns e.Firstname and e.LastName in the database table Employees with an alias of e.
An element named hire-dates that contains two attributes that retrieve the start hire date and end hire date in the database table Employees with an alias of e.
Right-click the Base SQL Query node, and select Insert / Element / Empty. The Element Node dialog box appears.





In the Name field, type employee.
Click OK. The ELEMENT node appears in the project tree.





TIP: Because keys uniquely identify rows in the database, we recommend that you use keys when possible to facilitate the performance of data retrieval.

Now, add an attribute key named id that will retrieve the employee ID from the database column e.EmpId. Because this attribute key will be a child of the element named employee, right-click the ELEMENT node named employee, and select Insert / Attribute node / As Select Expression. The Attribute Node dialog box appears.





In the Name field, type id.
The database column e.EmpId is already specified in the Select Expression drop-down list, so there is no need to select it.
Select the Is Key? checkbox.
Click OK. The ATTRIBUTE KEY node appears in the project tree.





Add an element named names that will contain the elements named first and last. Because this element is a child to the element named employee, right-click the ELEMENT node named employees, and select Insert / Element Node / Empty. The Element Node dialog box appears.





In the Name field, type names.
Click OK. The ELEMENT node appears in the project tree.





Add an element named first that will retrieve the first name of the employee from the database column e.FirstName. Because this element is a child to the element named names, right-click the ELEMENT node named names and select Insert / Element Node / as Select Expression. The Element Node dialog box appears.





In the Name field, type first.
From the Select Expression drop-down list, select e.FirstName.
Click OK. The ELEMENT node appears in the project tree.





Add an element named last that will retrieve the last name of the employee from the database column e.LastName. Because this element is a child to the element named names, right-click the ELEMENT node named names and select Insert / Element Node / as Select Expression. The Element Node dialog box appears.





In the Name field, type last.
From the Select Expression drop-down list, select e.LastName.
Click OK. The ELEMENT node appears in the project tree.





Add an element named hire-dates that will contain the attributes named start and end. Because this element is a child to the element named employee, right-click the ELEMENT node named employees, and select Insert / Element Node / Empty. The Element Node dialog box appears.





In the Name field, type hire-dates.
Click OK. The ELEMENT node appears in the project tree.





Add an attribute named start that will retrieve the start hire date of the employee from the database column e.StartDate. Because this attribute is a child to the element named hire-dates, right-click the ELEMENT node named hire-dates, and select Insert / Attribute Node / as Select Expression. The Attribute Node dialog box appears.





In the Name field, type start.
From the Select Expression drop-down list, select e.StartDate.
Click OK. The ATTRIBUTE node appears in the project tree.





Add an attribute named end that will retrieve the end hire date of the employee from the database column e.EndDate. Because this attribute is a child to the element named hire-dates, right-click the ELEMENT node named hire-dates, and select Insert / Attribute Node / as Select Expression. The Attribute Node dialog box appears.





In the Name field, type end.
From the Select Expression drop-down list, select e.EndDate.
Click OK. The ATTRIBUTE node appears in the project tree.

You have completed the part of the query that specifies which data to retrieve and defines the XML structure of the data in the result set.

If you want to see what the SQL/XML query looks like using the SQL/XML syntax, switch to Text view in the Builder by selecting the Text View tab. If the SQL/XML query you created is correct syntactically, the Builder allows you to switch from Tree view to Text view or the reverse.

Notice that the key designation we made in our example when we created the ATTRIBUTE node named id has been translated to the Connect for SQL/XML hint:

--{jxtr-hints key_expr=e.EmpId 

See "Using the SQL/XML Query in a Java Application" to learn how to specify other Connect for SQL/XML hints and how to code a SQL/XML query in a Java application.

Using the SQL/XML Query in a Java Application

You can cut and paste the SQL/XML query from the Text view of the Builder into a Java application.

The code example in this section can be found in the SQLXMLExample4.java file in the examples/src/examples/sqlxml directory in your Connect for SQL/XML installation directory.

The following code example performs the following tasks:

• Imports the necessary Java classes.

• Creates a connection to the database with the Connect for SQL/XML JDBC driver.

• Embeds the SQL/XML query you created in the preceding sections. Notice that each line of the query begins with:

sqlxml.append ( "

and ends with:

");

Also, notice that quotes in a Java string constant must be preceded by the Java escape character \, for example:

sqlXml.append ( " xmlelement(NAME \"hire-dates\", ");

sqlXml.append ( " xmlattributes(e.StartDate as \"start\",e.EndDate as \"end\") ");

TIP: Connect for SQL/XML hints can also be typed in the Text view of the Builder.

• Provides Connect for SQL/XML hints that specify processing options that are not available in the SQL/XML syntax. For example:

sqlXml.append ( "--{jxtr-hints null_replacement=NA;key_expr=e.EmpId;timestamp_encoding=odbc " );

The preceding code specifies the following hints:

• null_replacement replaces NULL values retrieved from the database with the letters "NA".

• timestamp_encoding specifies that the odbc timestamp-to-string conversion be used.

• key_expr specifies that the database column e.EmpId be used to uniquely identify the row in the database.

• Sends a description of the query result to System.out.

• Executes the SQL/XML query.

• Sends the result set to System.out.

• Closes the connection to the database with the Connect for SQL/XML JDBC driver and declares the main method.

package examples.sqlxml; 
 
import java.io.OutputStreamWriter; 
import java.sql.*; 
 
import com.ddtek.jdbc.jxtr.*; 
import examples.*; 
 
public class SQLXMLExample4 extends Example 
{ 
 
    /** 
     * Executes the 'SQL/XML Example4' example 
     */ 
    public void execute() throws Exception 
    { 
        // Output example description 
        System.out.println( "=================================" ); 
        System.out.println( "= SQL/XML Example4 demonstrates =" ); 
        System.out.println( "=================================" ); 
        System.out.println( "  (a) Specifying null replacement hint." ); 
        System.out.println( "  (b) Specifying timestamp encoding hint." ); 
 
        // Load properties from the resource 
        loadProperties(); 
 
        // Create the Connect for SQL/XML JDBC connection 
        connectWithConnectForSQLXML(); 
 
        // Build SQL/XML query 
        StringBuffer sqlXml = new StringBuffer(); 
        sqlXml.append ( "--{jxtr-hints null_replacement=NA;key_expr=
e.EmpId;timestamp_encoding=odbc " ); 
        sqlXml.append ( lineSeparator ); 
        sqlXml.append ( "select " ); 
        sqlXml.append ( "  xmlelement (NAME \"employee\", " ); 
        sqlXml.append ( "    xmlattributes(e.EmpId as \"id\"), " ); 
        sqlXml.append ( "    xmlelement(NAME \"names\", " ); 
        sqlXml.append ( "      xmlelement(NAME \"first\", e.FirstName), " ); 
        sqlXml.append ( "      xmlelement(NAME \"last\", e.LastName)), " ); 
        sqlXml.append ( "    xmlelement(NAME \"hire-dates\", " ); 
        sqlXml.append ( "      xmlattributes(e.StartDate as 
\"start\",e.EndDate as \"end\") "); 
        sqlXml.append ( "  )) as SQLXMLCOL1 " ); 
        sqlXml.append ( "from Employees e where e.EmpId = 12 " ); 
 
        // 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(); 
 
        // Close Connect for SQL/XML JDBC connection 
        disconnect(); 
    } 
 
    /** 
     * Main method. 
     */ 
    public static void main ( String[] args ) throws Exception 
    { 
        Example thisDemo = new SQLXMLExample4();     
        thisDemo.execute(); 
    } 
 
 
}