PREVNEXTINDEX

3 Creating Connect for SQL/XML Queries Using the Builder

This chapter explains how to create SQL/XML and jXTransformer queries using the DataDirect Query Builder for SQL/XML (the Builder). The Builder is a Java application that makes it easy for you to create SQL/XML and jXTransformer queries without having detailed knowledge of the query syntax.

For more information about:

HTML-based online help for the Builder is placed on your system during the installation of your DataDirect product. To access help for the Builder, you must have Internet Explorer 5.x or higher, or Netscape 4.x or higher installed. Open the Builder help by selecting Help / Contents.

NOTE: Depending on your configuration, you may have to configure the Web browser as described in "Changing the GUI's General Appearance" before you can use the Builder help or the Web links in the Help menu.

For installation requirements and instructions for Connect for SQL/XML and the Builder, refer to the Connect for SQL/XML Installation Guide.

Working with the Builder

Using the Builder, you can create and store SQL/XML and jXTransformer queries in a SQL/XML Builder project file. Builder project files for SQL/XML queries have the file extension .cfb; Builder project files for jXTransformer queries (and jXTransformer write statements) have the file extension .jxb.

The Builder allows you to create SQL/XML or jXTransformer queries, execute the queries, and view the resulting XML using the following types of views:

Text view also allows you to execute jXTransformer write statements. See "Executing jXTransformer Write Statements" for instructions.

Using Tree and Text Views

Using Tree view, you can create and modify SQL/XML and jXTransformer queries without having to know the syntax of their respective query syntax. The SQL/XML and jXTransformer query constructs are represented as nodes in a Builder project tree, similar to a DOM tree, as shown in Figure 3-1. You can structure the data you retrieve from the database any way you want it in the resulting XML by adding, modifying, deleting, and moving nodes. The following figure shows a Tree view of a SQL/XML query.

Figure 3-1. jXTransformer Query in Tree View

SQL/XML Statement window in Tree view

Each node in the Builder project tree indicates the type of query construct that node represents. See "Using Project Tree Nodes" for a list of the node types you can use in the Builder project tree.

When you create SQL/XML and jXTransformer queries using a Tree view, you can easily see the query syntax used in the source of the query by switching to a Text view. The following figure shows a Text view of a SQL/XML query.

The same query shown in Tree view in Figure 3-1 is displayed in Text view in Figure 3-2.

Figure 3-2. jXTransformer Query in Text View

SQL/XML Statement window in Text view

Conversely, if you want to work in Text view and create a query using the SQL/XML or jXTransformer query syntax, you can view the resulting changes in the Builder project tree by switching to Tree view.

For more information about:

NOTE: Before you can switch between Tree view and Text view, the syntax of your query must be correct. The Builder generates a message if an error is encountered. If an error is encountered, you cannot switch views until you correct the error.

Using Project Tree Nodes

The node types you can create in a Builder project in Tree view depend on whether you are creating a SQL/XML or jXTransformer query.

SQL/XML Queries

Table 3-1 lists the SQL/XML query node types you can create in a Builder project in Tree view and provides a description of each node type. Notice that most of the node types correspond to operators in the SQL/XML query syntax. See Chapter 4 "Syntax of SQL/XML Queries" for information about SQL/XML query syntax.

Table 3-1. Builder Project Tree Nodes for SQL/XML Queries 
SQL/XML Nodes
Description
project node
Specifies the parent node for the SQL/XML query. This node is required and is created when you create a Builder project. It cannot be modified, moved, or deleted. This node is used only in the Tree view and does not correspond to a SQL/XML operator.
Base SQL Query node
Specifies a SQL query on which all or part of the SQL/XML query is based. This node is required. When specifying table names, you must use unique table aliases. This node is used only in Tree view and does not correspond to a SQL/XML operator.
See "Using Base SQL Query Nodes" for more information.
ELEMENT node
Specifies an XML element. This node can have multiple values and mixed content. This node corresponds to the XMLELEMENT operator.
ATTRIBUTE node
Specifies an XML attribute associated with an ELEMENT node. This node corresponds to a single attribute specified by the XMLATTRIBUTES operator.
SELECT EXPR node
Specifies a Select expression value for an ELEMENT node.
CONSTANT node
Specifies a constant value for an ELEMENT node.
SELECT EXP KEY
Specifies that a value that is a Select expression for an ELEMENT node is also a key. This node corresponds to a Connect for SQL/XML hint; it does not correspond to a SQL/XML operator.
ATTRIBUTE KEY
Specifies that a value that is a Select expression for an ATTRIBUTE node is also a key. This node corresponds to a Connect for SQL/XML hint; it does not correspond to a SQL/XML operator.
CONCAT node
Specifies a forest of elements that is produced by concatenating a list of XML values. This node corresponds to the XMLCONCAT operator.
FOREST node
Specifies a forest of elements that is produced from a list of arguments. This node corresponds to the XMLFOREST operator.
FOREST ELEMENT node
Specifies an element that belongs to a forest of elements. This node corresponds to an argument accepted by the XMLFOREST operator.
AGG node
Specifies a forest of elements that is produced from a collection of XML elements. This node corresponds to the XMLAGG operator.

See "Creating and Modifying SQL/XML Queries in Tree View" for instructions on using these nodes to create SQL/XML queries in Tree view of the Builder.

jXTransformer Queries

Table 3-2 lists the jXTransformer node types you can create in a Builder project in Tree view and provides a description of each node type. Notice that most of the node types correspond to keywords in the jXTransformer query syntax. See Chapter 5 "Syntax of jXTransformer Queries" for information about jXTransformer query syntax.

Table 3-2. Builder Project Nodes for jXTransformer Queries 
jXTransformer Nodes
Description
project node
Specifies the parent node for the jXTransformer query, which can contain one or multiple sub-queries. This node is required and is created when you create a jXTransformer Builder project. It cannot be modified, moved, or deleted. This node is used only in the Tree view and does not correspond to a jXTransformer keyword.
ROOT ELEMENT node
Specifies the root element to be used for the resulting XML document. When you turn on the document header, a ROOT ELEMENT node is inserted in the project tree with the default root element name jxtr-result. This node corresponds to the xml_element keyword when the keyword is used to specify a root element.
COMMENT node
Specifies comments in the XML document header. This node corresponds to the xml_comment keyword.
PROCESSING INSTRUCTION node
Specifies an XML processing instruction. This node corresponds to the xml_pi keyword.
EXTERNAL DTD node
Specifies an external public or private DTD. This node corresponds to the xml_external_dtd keyword.
Base SQL Query node
Specifies a SQL query on which all or part of the jXTransformer query is based. This node is required. When specifying table names, you must use unique table aliases. This node is used only in Tree view and does not correspond to a jXTransformer keyword.
See "Using Base SQL Query Nodes" for more information.
ELEMENT node
Specifies an XML element. This node can have multiple values and mixed content. This node corresponds to the xml_element keyword.
ATTRIBUTE node
Specifies an XML attribute associated with an ELEMENT node or a ROOT ELEMENT node. This node corresponds to the xml_attribute keyword.
HIDE node
Specifies a node that allows you to retrieve information from the database you do not want to include in the resulting XML document. This node corresponds to the xml_hide keyword.
CDATA node
Specifies an XML CDATA section. This node corresponds to the xml_cdata keyword.
NAMESPACE node
Specifies an XML namespace associated with an ELEMENT node or a ROOT ELEMENT node. This node corresponds to the xml_namespace keyword.
SELECT EXPR node
Specifies Select expression value for an ELEMENT node.
CONSTANT node
Specifies a constant value for an ELEMENT node.
SELECT EXP KEY
Specifies that a value that is a Select expression for an ELEMENT node is also a key. This node corresponds to the xml_element_key keyword.
ATTRIBUTE KEY
Specifies that a value that is a Select expression for an ATTRIBUTE node is also a key. This node corresponds to the xml_attribute_key keyword.
HIDE KEY
Specifies that a value that is a Select expression for a HIDE node is also a key. This node corresponds to the xml_hide_key keyword.

See "Creating and Modifying jXTransformer Queries in Tree View" for instructions on using these nodes to create jXTransformer queries in Tree view of the Builder.

Working with Project Tree Nodes

You can expand or collapse any node in the project tree by double-clicking that node or by single-clicking the + (plus sign) or - (minus sign) for that node. You can also expand or collapse any node and its child nodes by right-clicking the node and selecting Expand Branch or Collapse Branch.

Builder menu commands in the Tree view are position-sensitive. When you create a node, the new node becomes a child of the selected node. For example, if you have an ELEMENT node selected when you select Insert / Attribute Node, the ATTRIBUTE node will be created as a child of that ELEMENT node.

Also, to help you create a valid query that complies with the SQL/XML or jXTransformer query syntax, the Builder menu commands that are available depend on the selected node.

Using Base SQL Query Nodes

The Base SQL Query node is a SQL query that forms a base for all or part of your query. It is a required node in the Builder project tree. It allows you to decide which data you want to retrieve from the database before you create the query and facilitates the process of constructing the query. Figure 3-3 shows a SQL/XML query in Tree view. Notice the Base SQL Query node is the first node under the project node named employees.

Figure 3-3. Base SQL Query in SQL/XML Query

SQL/XML Statement window in Tree view showing Base SQL Query node

Starting the Builder

How you start the Builder depends on your platform:

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.

Customizing the Builder

You can customize the following settings in the Builder:

NOTE: You may have to configure this setting before you can access the online help for the Builder and use the Web links in the Help menu. See "Changing the GUI's General Appearance" for instructions.

NOTE: This feature is reserved for DataDirect Technologies use; only use when instructed to do so by DataDirect Technologies.

Changing the GUI's General Appearance

  1. Select Tools / Options. The General tab appears.


    2. General tab of the Options dialog box

  2. In the Look and Feel group, complete the following information:

    3. Look and Feel: From the drop-down list, select the Java theme you want to use. The default is Metal. The screenshots in this book use the Windows Java theme.

    Font Size: From the drop-down list, select the font size you want the Builder to use for text in menus and dialog boxes. The default is 12 points. The font size for the text and tree views in the SQL/XML Statement window and jXTransformer Statement window can be specified on the Editor tab.

  3. In the Other group, complete the following information:

Web Browser Command: Type the full path of the Web browser executable to be used to display the online help and access the Web links from the Help menu. Append a space and %1to the executable. For example:

C:\Program Files\Internet Explorer\iexplore.exe %1

Debug logging: Debug logging is used for troubleshooting and dumps debug messages to your standard error stream. It is turned off by default. Check the Debug logging check box to turn debug logging on.

Changing the Text Editor

  1. Select Tools / Options. Then, select the Editor tab.


    2. Editor tab of the Options dialog box

  2. To change the appearance of the editor in the:
  3. To change the appearance of the editor in the Text view of the SQL/XML Statement window and jXTransformer Statement window, complete the following information:

    4. Font: From the drop-down list, choose the font you want to use in the text editor.

    Size: From the drop-down list, choose the size of the font you want to use in the text editor. The default is 12 points.

    If you want to define a style for an item in the text editor, which include comments, SQL/XML query operators or jXTransformer query keywords, SQL keywords, numerical values, and strings, select that item in the Style group. You can distinguish any listed item by color, bold type, italic type, or any combination of these characteristics.

    NOTE: You can disable any style without deleting its definition by selecting the Disable check box in the Style group.

    Select your choices, and click OK.

  4. To change the appearance of the editor in the XML window, complete the following information:

Font: From the drop-down list, choose the font you want to use in the text editor.

Size: From the drop-down list, choose the size of the font you want to use in the text editor. The default is 12 points.

Select your choices, and click OK.

Creating a Builder Project

From the main menu, select File / New Project. The New Project dialog box appears, prompting you to select which type of project to create: SQL/XML or jXTransformer.

From the drop-down list, select the type of Builder project to create:

An untitled project node appears in Tree view of the SQL/XML Statement window or jXTransformer Statement window, depending on the type of Builder project you chose to create. The following figure shows a newly created SQL/XML project in the SQL/XML Statement window.

SQL/XML Statement window in Tree view, with an untitled project node

When you save the Builder project to a file, the project node is renamed to the name of the Builder project file. You cannot modify, move, or delete this node.

For instructions on creating and modifying:

When you are ready to save your Builder project, select File / Save Project As. Specify the filename of the project, and click Save.

Opening a Builder Project

From the main menu, select File / Open Project. A dialog box allows you to browse and select a project to open. Select the Builder project you want to open, and click OK.

NOTE: Remember that Builder projects for SQL/XML queries have a file extension of .cfb; Builder projects for jXTransformer queries and jXTransformer write statements have a file extension of .jxb.

A SQL/XML query or jXTransformer query appears in the Tree view or the Text view of the Builder, depending on which view was active when the project was last saved. A jXTransformer write statement appears in Text view only.

Closing a Builder Project

When you are ready to close a Builder project, select File / Close Project. You are prompted to confirm whether you want to save the project before it is closed. Click OK to save the project. When this project is opened again in the Builder, it will appear in the view that was active when the project was last saved.

Creating and Modifying SQL/XML Queries in Tree View

This section describes how to create and modify SQL/XML queries in Tree view and provides instructions on performing the following tasks:

NOTE: Builder menu commands are position-sensitive. When creating Builder project tree nodes, the resulting hierarchy depends on the node you have selected when you perform the menu command.

See "Creating and Modifying SQL/XML and jXTransformer Statements in Text View" for instructions on creating and modifying queries and statements in Text view of the Builder.

Creating Base SQL Query Nodes

A Base SQL Query node contains the SQL query on which the SQL/XML query is based. A Base SQL Query node is required for each SQL/XML sub-query within a Builder project. See "Using Base SQL Query Nodes" for information about using Base SQL Query nodes.

To create a Base SQL Query node:

  1. Right-click the project node, and select Insert / Base SQL Query node. The Base SQL Query Node dialog box appears.


    2. Base SQL Query Node dialog box

  2. Using a simple Select statement, type a SQL query or click the From File button to navigate to a text file that contains a SQL query.
  3. Click OK.

The Builder checks the SQL query for syntax. If an error is detected, a dialog box appears describing the error. If no errors are detected, a Base SQL Query node appears in the Builder project tree.

Once a Base SQL Query node is created, you can construct the SQL/XML query based on the Base SQL Query node, creating other types of nodes for XML elements, attributes, and so on.

Creating XML Elements

You specify XML elements in the Tree view of the Builder using ELEMENT nodes. Because ELEMENT nodes can have multiple values and mixed content, values of ELEMENT nodes are shown as child nodes. You can create an ELEMENT node with any of the following values:

Select Expression

  1. Right-click a Base SQL Query node or an ELEMENT node, and select Insert / Element Node / as Select Expression. The Element Node dialog box appears.


    2. Element Node dialog box

  2. In the Name field, type the name of the XML element.
  3. From the Select Expression drop-down list, select an available Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
  4. Select the Is Key? checkbox if the Select expression associated with this node is the key or part of a multi-value key. Keys uniquely identify each row selected by the base SQL query.
  5. Click OK. The ELEMENT node appears in the project tree with a Select expression node as its child.

Constant

  1. Right-click a Base SQL Query node or an ELEMENT node, and select Insert / Element Node / as Constant. The Element Node dialog box appears.


    2. Element Node dialog box

  2. In the Name field, type the name of the XML element.
  3. In the Value field, type a constant value. This can be any character string literal.
  4. Click OK. The ELEMENT node appears in the project tree with a CONSTANT node as its child.

Empty

  1. Right-click a Base SQL Query node or an ELEMENT node, and select Insert / Element Node / Empty. The Element Node dialog box appears.


    2. Element Node dialog box

  2. In the Name field, type the name of the XML element.
  3. Click OK. The ELEMENT node appears in the project tree.

Creating XML Attributes

You specify XML attributes in the Tree View of the Builder using ATTRIBUTE nodes. You can create an ATTRIBUTE node with any of the following values:

Select Expression

  1. Right-click an ELEMENT node, and select Insert / Attribute Node / as Select Expression. The Attribute Node dialog box appears.


    2. Attribute Node dialog box

  2. In the Name field, type the name of the XML attribute.
  3. From the Select Expression drop-down list, select an available Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
  4. Select the Is Key? checkbox if the Select expression associated with this node is the key or part of a multi-value key. Keys uniquely identify each row selected by the base SQL query.
  5. Click OK. The ATTRIBUTE node appears in the project tree.

Constant

  1. Right-click an ELEMENT node and select Insert / Attribute Node / as Constant. The Attribute Node dialog box appears.


    2. Attribute Node dialog box

  2. In the Name field, type the name of the XML attribute.
  3. In the Value field, type a constant value. This can be any character string literal.
  4. Click OK. The ATTRIBUTE node appears in the project tree.

Assigning Select Expression Values to ELEMENT Nodes

Because XML elements can have multiple values and mixed content, you can assign a Select expression value to an empty ELEMENT node.

To assign a Select expression value to an ELEMENT node:

  1. Select Insert / Select Expression Node. The Select Expression Node dialog box appears.


    2. Select Expression Node dialog box

  2. From the Select Expression drop-down list, select an available Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
  3. Select the Is Key? checkbox if the Select expression associated with this node is the key or part of a multi-value key. Keys uniquely identify each row selected by the base SQL query.
  4. Click OK. The SELECT EXPR node appears in the project tree as a child of the selected ELEMENT node.

Assigning Constant Values to ELEMENT Nodes

Because XML elements can have multiple values and mixed content, you can assign a constant value to an empty ELEMENT node.

To assign a constant value to an ELEMENT node:

  1. Select Insert / Constant Node. The Constant Node dialog box appears.


    2. Constant Node dialog box

  2. In the Value field, type a constant value. This can be any character string literal.
  3. Click OK. The CONSTANT node appears in the project tree as a child of the selected ELEMENT node.

Creating a Forest of XML Elements

There are multiple ways to create a forest of XML elements, which is a collection of XML elements. The Builder allows you to create a forest of XML elements in Tree view using the following types of nodes:

XML Forest

A FOREST node produces a forest of XML elements from a given list of arguments. To create a FOREST node, select Insert / XML Forest Node. The FOREST node appears in the project tree. Add one or more Forest Elements as described in the next section.

Forest Elements

You can create an FOREST ELEMENT node with any of the following values:

Select Expression
  1. Right-click a FOREST node, and select Insert / Forest Element Node / as Select Expression. The Forest Element Node dialog box appears.


    2. Element Node dialog box

  2. In the Name field, type the name of the XML forest element.
  3. From the Select Expression drop-down list, select an available Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
  4. Select the Is Key? checkbox if the Select expression associated with this node is the key or part of a multi-value key. Keys uniquely identify each row selected by the base SQL query.
  5. Click OK. The FOREST ELEMENT node appears in the project tree with a Select expression node as its child.
Constant
  1. Right-click a FOREST node, and select Insert / Forest Element Node / as Constant. The Forest Element Node dialog box appears.


    2. Element Node dialog box

  2. In the Name field, type the name of the XML forest element.
  3. In the Value field, type a constant value. This can be any character string literal.
  4. Click OK. The FOREST ELEMENT node appears in the project tree with a CONSTANT node as its child.
Empty
  1. Right-click a FOREST node, and select Insert / Forest Element Node / Empty. The Forest Element Node dialog box appears.


    2. Element Node dialog box

  2. In the Name field, type the name of the XML forest element.
  3. Click OK. The FOREST ELEMENT node appears in the project tree.

XML Concatenation

CONCAT nodes produce a forest of XML elements by concatenating a list of XML values.

To create a forest of XML elements using XML concatenation:

  1. Select Insert / XML Concatenation Node. The CONCAT node appears in the project tree.
  2. Select the CONCAT node and add one or multiple nodes of the following types to specify the list of XML values:

XML Aggregate

AGG nodes produce a forest of XML elements from a collection of XML elements that is specified using a single XML value.

To create a forest of XML elements using XML aggregate:

  1. Select Insert / XML Aggregate Node. The XML Aggregate Node dialog box appears allowing you to optionally specify an Order by condition.



  2. If you want to sort your results, in the Order by field, type a value by which you want the returned result set to be sorted. You can specify any alternative for a sort list as defined in the document ISO/IEC 9075-2.

    3. If you do not want to sort your results, leave the Order by field empty.

  3. Click OK. The AGG node appears in the project tree.
  4. Select the AGG node and add one node of the following node types to specify a collection of XML elements:

Modifying Nodes

Right-click the node you want to modify, and select Edit. Change the information in the dialog box as necessary. When you are satisfied with your changes, click OK.

NOTES:

Moving Nodes

Select the node you want to move. Then, drag and drop the node to the location you want it to appear in the project tree.

NOTES:

Deleting Nodes

Right-click the node you want to delete and select Delete. If the selected node has children, a window appears asking you to confirm the deletion of the node and all its children. To confirm, click OK.

NOTES:

Creating and Modifying jXTransformer Queries in Tree View

This section describes how to create and modify jXTransformer queries in Tree view and provides instructions on performing the following tasks:

NOTE: Builder menu commands are position-sensitive. When creating Builder project tree nodes, the resulting hierarchy depends on the node you have selected when you perform the menu command.

See "Creating and Modifying SQL/XML and jXTransformer Statements in Text View" for instructions on creating and modifying queries in Text view of the Builder.

Turning on the Document Header

To generate a complete XML document instead of an XML document fragment from a jXTransformer query, you must turn on the document header by selecting Insert / Document Header. If you do not turn on the document header, an XML document fragment is generated when the query is executed.

Turning on the document header automatically inserts a ROOT ELEMENT node in the project tree with a default root element name of jxtr-result. You can change the default root element name by editing the ROOT ELEMENT node. See "Modifying Nodes" for instructions. Only one XML root element is allowed in each XML document.

NOTE: When you turn off the document header, the ROOT ELEMENT node, and any COMMENT, PROCESSING INSTRUCTION, and EXTERNAL DTD nodes are deleted.

Specifying Comments

You can specify one or multiple comments in the document header only. Comments are specified in the Tree view of the Builder by using COMMENT nodes, with the project node as the parent. To specify multiple comments, create multiple COMMENT nodes, one for each comment.

NOTE: To create a COMMENT node, you first must turn on the document header. See "Turning on the Document Header" for instructions.

To specify a comment:

  1. Select the project node, and select Insert / Comment. The Comment Node dialog box appears.


    2. Comment Node dialog box

  2. In the Comment field, type a comment.
  3. Click OK. A COMMENT node with the specified comment appears in the project tree.

Specifying Processing Instructions

You can create one or multiple processing instructions in the resulting XML document. For example, if the generated XML document will be viewed in a browser, you may want to specify a processing instruction so that a specific XSL stylesheet is used when the XML document is viewed. Processing instructions are specified in the Tree view of the Builder using PROCESSING INSTRUCTION nodes, with the project node as the parent. To specify multiple processing instructions, create multiple PROCESSING INSTRUCTION nodes, one node for each processing instruction.

NOTE: To create a PROCESSING INSTRUCTION node, you first must turn on the document header. See "Turning on the Document Header" for instructions.

To specify a processing instruction:

  1. Select the project node, and select Insert / Processing Instruction. The Processing Instruction Node dialog box appears.


    2. Processing Instruction Node dialog box

  2. In the Processing instruction target field, type the target of the processing instruction. For example, if you are specifying a processing instruction for an XSL stylesheet, you may want to type xsl-stylesheet.
  3. In the Processing instruction field, type a valid XML processing instruction. For example, if you are specifying a processing instruction for an XSL stylesheet named common.xsl, you would type:

    4. type="text/xsl" href="file://common.xsl"

  4. Click OK. A PROCESSING INSTRUCTION node with the specified processing instruction appears in the project tree.

Specifying an External DTD

You can specify an external private or public DTD in the document header. External DTDs are specified in the Tree view of the Builder using EXTERNAL DTD nodes, with the project node as the parent.

NOTE: To create an EXTERNAL DTD node, you first must turn on the document header. See "Turning on the Document Header" for instructions.

To view a DTD, you can open the DTD in the Open DTD window. See "Opening an XML DTD" for instructions.

To specify an external DTD:

  1. Select the project node, and select Insert / External DTD. The External DTD Node dialog box appears.


    2. External DTD Node dialog box

  2. If specifying a public external DTD, in the Public Identifier field, type the public identifier of the public external DTD. Do not specify anything in this field if specifying a private external DTD.
  3. In the URI field, type the Uniform Resource Identifier that identifies the external DTD.
  4. Click OK. An EXTERNAL DTD node appears in the project tree.

Creating Base SQL Query Nodes

A Base SQL Query node contains the SQL query on which the jXTransformer query is based. A Base SQL Query node is required for each jXTransformer sub-query within a Builder project. See "Using Base SQL Query Nodes" for information about using Base SQL Query nodes.

To create a Base SQL Query node:

  1. Right-click the project node or a ROOT ELEMENT node, and select Insert / Base SQL Query node. The Base SQL Query Node dialog box appears.


    2. Base SQL Query Node dialog box

  2. Using a simple Select statement, type a SQL query or click the From File button to navigate to a text file that contains a SQL query.
  3. Click OK.

    4. The Builder checks the SQL query for syntax. If an error is detected, a dialog box appears describing the error. If no errors are detected, a Base SQL Query node appears in the project tree.

    Once a Base SQL Query node is created, you can construct the jXTransformer query based on the Base SQL Query node, creating other types of nodes for jXTransformer elements, attributes, and so on.

    Creating XML Elements

    You specify XML elements in the Tree view of the Builder using ELEMENT nodes. Because ELEMENT nodes can have multiple values and mixed content, values of ELEMENT nodes are shown as child nodes. You can create an ELEMENT node with any of the following values:

    • A Select expression, which can be any Select expression specified in the Base SQL Query node (for example, a database column such as e.LastName).
    • A constant, which can be any character string literal (for example, a literal such as 8 or TRUE).
    • Empty (no value). Use this value if you want the node to contain only XML subelements such as other XML elements, mixed content, or a nested jXTransformer query.

    Select Expression

    1. Right-click a Base SQL Query node or an ELEMENT node, and select Insert / Element Node / as Select Expression. The Element Node dialog box appears.


      2. Element Node dialog box

    2. In the Name field, type the name of the XML element.
    3. From the Select Expression drop-down list, select an available Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
    4. Select the Is Key? checkbox if the Select expression associated with this node is the key or part of a multi-value key. Keys uniquely identify each row selected by the base SQL query.
    5. Click OK. The ELEMENT node appears in the project tree with a Select expression node as its child.

    Constant

    1. Right-click a Base SQL Query node or an ELEMENT node, and select Insert / Element Node / as Constant. The Element Node dialog box appears.


      2. Element Node dialog box

    2. In the Name field, type the name of the XML element.
    3. In the Value field, type a constant value. This can be any character string literal.
    4. Click OK. The ELEMENT node appears in the project tree with a CONSTANT node as its child.

    Empty

    1. Right-click a Base SQL Query node or an ELEMENT node, and select Insert / Element Node / Empty. The Element Node dialog box appears.


      2. Element Node dialog box

    2. In the Name field, type the name of the XML element.
    3. Click OK. The ELEMENT node appears in the project tree.

    Creating XML Attributes

    You specify XML attributes in the Tree View of the Builder using ATTRIBUTE nodes. You can create an ATTRIBUTE node with any of the following values:

    • A Select expression, which can be any Select expression specified in the Base SQL Query node (for example, a database column such as e.LastName).
    • A constant, which can be any character string literal (for example, a literal such as 8 or TRUE).
    • A SQL query (typically used to construct IDREF values for an XML attribute). This query must select only one table column.

    Select Expression

    1. Right-click an ELEMENT node, and select Insert / Attribute Node / as Select Expression. The Attribute Node dialog box appears.


      2. Attribute Node dialog box

    2. In the Name field, type the name of the XML attribute.
    3. From the Select Expression drop-down list, select an available Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
    4. Select the Is Key? checkbox if the Select expression associated with this node is the key or part of a multi-value key in the database. Keys uniquely identify each row selected by the base SQL query.
    5. Click OK. The ATTRIBUTE node appears in the project tree.

    Constant

    1. Right-click an ELEMENT node and select Insert / Attribute Node / as Constant. The Attribute Node dialog box appears.


      2. Attribute Node dialog box

    2. In the Name field, type the name of the XML attribute.
    3. In the Value field, type a constant value. This can be any character string literal.
    4. Click OK. The ATTRIBUTE node appears in the project tree.

    SQL Query

    1. Right-click an ELEMENT node and select Insert / Attribute Node / as SQL Query. The Attribute Node dialog box appears.


      2. Attribute Node dialog box

    2. In the Name field, type the name of the XML attribute.
    3. From the Select Expression field, type a SQL query or click the From File button to navigate to a text file that contains a SQL query. This query must select only one table column.
    4. Click OK. The ATTRIBUTE node appears in the project tree.

    Specifying Hide Information

    You can retrieve information from the database that you do not want to include in the resulting XML document using HIDE nodes. Hide information typically is used to select database columns that are a key or part of a multi-value key that you do not want to display in the resulting XML document.

    To specify hide information:

    1. Right-click the Base SQL Query node or an ELEMENT node, and select Insert / Hide Node. The Hide Node dialog box appears.


      2. Hide Node dialog box

    2. From the Select Expression drop-down list box, select a Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
    3. Select the Is Key? checkbox if the select expression associated with this node is the key or part of a multi-value key. Keys uniquely identify each row selected by the base SQL query.
    4. Click OK. The HIDE node appears in the project tree.

    Creating XML CDATA Sections

    You can create CDATA sections in the Tree view of the Builder using CDATA nodes. You can create a CDATA node with any of the following values:

    • Select expression, which can be any Select expression specified in the Base SQL Query node (for example, a database column such as e.LastName).
    • Constant, which can be any character string literal (for example, a literal such as 8 or TRUE).

    Select Expression

    1. Right-click the Base SQL Query node or an ELEMENT node, and select Insert / CDATA Node / as Select Expression. The CDATA Node dialog box appears.


      2. CDATA Node dialog box

    2. From the Select Expression drop-down list, select a Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
    3. Click OK. The CDATA node appears in the project tree.

    Constant

    1. Right-click the Base SQL Query node or an ELEMENT node, and select Insert / CDATA Node / as Constant. The CDATA Node dialog box appears.


      2. CDATA Node dialog box

    2. In the Value field, type a constant value or click the From File button to navigate to a file containing the constant value. This can be any character string literal.
    3. Click OK. The CDATA node appears in the project tree.

      4. Specifying XML Namespaces

      An XML namespace allows you to create a namespace definition for an XML element. You can create XML namespace definitions in the Tree view of the Builder using NAMESPACE nodes.

      To specify a namespace:

      1. Right-click an ELEMENT node or a ROOT ELEMENT node, and select Insert / Namespace Node. The Namespace Node dialog box appears.


        2. Namespace Node dialog box

      2. (Optional) In the Prefix field, type a prefix for the namespace. If you do not specify a prefix, the default XML namespace will be defined.
      3. In the Namespace URI field, type a URI to identify the namespace.
      4. Click OK. The NAMESPACE node appears in the project tree.

      Assigning Select Expression Values to ELEMENT Nodes

      Because XML elements can have multiple values and mixed content, you can assign a Select expression value to an empty ELEMENT node.

      To assign a Select expression value to an ELEMENT node:

      1. Select Insert / Select Expression Node. The Select Expression Node dialog box appears.


        2. Select Expression Node dialog box

      2. From the Select Expression drop-down list, select an available Select expression. This list is populated with the Select expressions specified in the Base SQL Query node.
      3. Click OK. The SELECT EXPR node appears in the project tree as a child of the selected ELEMENT node.

      Assigning Constant Values to ELEMENT Nodes

      Because XML elements can have multiple values and mixed content, you can assign a constant value to an empty ELEMENT node.

      To assign a constant value to an ELEMENT node:

      1. Select Insert / Constant Node. The Constant Node dialog box appears.


        2. Constant Node dialog box

      2. In the Value field, type a constant value. This can be any character string literal.
      3. Click OK. The CONSTANT node appears in the project tree as a child of the selected ELEMENT node.

      Modifying Nodes

      Right-click the node you want to modify, and select Edit. Change the information in the dialog box as necessary. When you are satisfied with your changes, click OK.

      NOTES:

      • You cannot modify a project node.
      • Changes to a validated Base SQL Query node may invalidate nodes in the project tree.
      • For ELEMENT nodes, you can only change the name of the node because ELEMENT nodes can contain multiple value nodes.

      Moving Nodes

      Select the node you want to move. Then, drag and drop the node to the location you want it to appear in the project tree.

      NOTES:

      • You cannot move the project node.
      • The Builder restricts moves to valid choices as defined by the jXTransformer query syntax.

      Deleting Nodes

      Right-click the node you want to delete and select Delete. If the selected node has children, a window appears asking you to confirm the deletion of the node and all its children. To confirm, click OK.

      NOTES:

      • When you delete a parent node with children nodes, the children nodes are deleted also.
      • You cannot delete the project node.

      Creating and Modifying SQL/XML and jXTransformer Statements in Text View

      To create and modify SQL/XML and jXTransformer statements in Text view, you must be familiar with their respective syntax. Using valid syntax, you can type the statement in the Text view of the Builder. Figure 3-4 shows a SQL/XML query in the Text view of the SQL/XML Statement window.

      Figure 3-4. SQL/XML Query in Text View

      SQL/XML Statement window in Text view

      You can also copy or import an existing SQL/XML or jXTransformer query or statement into Text view of the Builder and modify them as needed. See "Importing a Query or Statement" for instructions.

      For more information about:

      Importing a Query or Statement

      You can only import a query or statement into Text view of the Builder.

      To import a query or statement:

      1. Select File / Import Statement. The Open Statement Document dialog box appears allowing you to specify a path to an existing query or statement.


        2. Open Statement Document dialog box

      2. In the Path field, type the path to the file containing the query or statement, or click the Browse (...) button to navigate to the file.
      3. Click OK. The contents of the specified file appear in the SQL/XML Statement window or jXTransformer Statement window.

      Checking Query or Statement Syntax

      When you switch between the Tree and Text views, the Builder automatically checks the syntax of your query or statement. If an error is encountered, a message is generated. You can also check the syntax of a query or statement by selecting Project / Check Statement Syntax.

      Connecting to the Database

      To browse the database or execute a query or statement, a JDBC connection to the database using a DataDirect Technologies JDBC driver is required. The JDBC connection can be accomplished using a JDBC connection URL or JDBC data source.

      To connect to the database:

      Select Project / Connect to Database. The Open JDBC Connection dialog box appears.

      Open JDBC Connection dialog box

      The Connection URL option is selected by default. If you want to connect to the database using:

      Connecting Using JDBC Connection URLs

      When you connect to the database, the Open JDBC Connection dialog box appears with the Connection URL option selected by default.

      Open JDBC Connection dialog box

      To connect using a JDBC connection URL:

      1. In the Driver group, type or select the driver you want to use for the connection from the drop-down list. You can enter any driver class specified in your classpath or enter any named driver that you have explicitly configured.
      2. In the Connection group, complete the following information:

        3. URL: Select the URL you want to use for the connection from the drop-down list or type a URL.

        UID: Type a user name for the database.

        PWD: Type a password for the database.

      3. Click OK. When you are connected, a message confirming the connection appears in the Output window.

      Connecting Using JDBC Data Sources

      NOTE: To use JDBC data sources with the Builder if you are not using JDK 1.4 or higher, you must install the JNDI service providers used by your data sources and you must install the following Java packages and include them in your classpath:

      • JDBC 2.0 Optional Package
      • Java Naming and Directory Interface Package

      To connect to the database using a JDBC data source, select the data source option on the Open JDBC Connection dialog box. The fields on the dialog box change to accommodate information required for a data source.

      Open JDBC Connection dialog box

      To connect using a JDBC data source:

      1. In the JNDI group, complete the following information:

        2. Context Factory: Type the name of the JNDI context factory to be used, or select the name from the drop-down list.

        Provider URL: Type the URL that locates the JNDI provider to be used, or select a URL from the drop-down list.

      2. In the Data Source group, complete the following information:

        3. Name: Type the name of the data source you want to use for the connection.

        UID: Type a user name for the database.

        PWD: Type a password for the database.

      3. Click OK. When you are connected, a message confirming the connection appears in the Output window.

      Executing a Query or Statement

      Once you execute a query or statement, you can view the results in Tree view or Text view by selecting the appropriate tab at the bottom of the results window. The type of results that are returned depend on the type of query or statement being executed:

      • A SQL/XML query returns a result set, where one or multiple columns in the result set can contain XML type values. The Builder displays these result sets by concatenating all columns or rows after converting the values to strings.
      • A jXTransformer query returns an XML document or XML document fragment.
      • A jXTransformer write statement returns a list of update counts.

      To execute a query or statement:

      1. Select Project / Execute Statement. If you are not connected to the database, you first must make a JDBC connection. The Open JDBC Connection dialog box appears.


        2. Open JDBC Connection dialog box

        The Connection URL option is selected by default. If you want to connect to the database using:

      2. Parameter markers are placeholders for values, represented by question marks (?). If a query contains parameter markers, the Statement Parameters dialog box appears, prompting you for the parameter marker values.


        3. Query Parameters dialog box

        In the Value field, type the value of the parameter marker or select the Null checkbox to set the parameter marker to a null value. Then, click OK.

        The Execute Statement dialog box appears.


        Execute Query dialog box

      3. In the Output group, select one of the following options:
        • To Window causes the results to display in a separate window. You can save the results to a file from this window by selecting File / Save as.
        • To File causes the results to be saved directly to a file instead of displaying it in the Builder. Type the path and name of the file in the associated field, or click the Browse (...) button to browse to a file.
      4. In the Options group, complete the following information:

        5. Execute using ResultSetWrapper: This option is enabled only for jXTransformer query projects that execute a SQL query; it is disabled for all SQL/XML queries. Check this option to execute the SQL query using an attribute-centric or element-centric formula.

        Mode: From the drop-down list, select attribute-centric or element-centric to choose a document structure for the SQL query.

        See "Choosing an XML Document Structure" for information about attribute-centric and element-centric document structures.

        Beautify: This option is turned on by default. The Beautify option formats the content of XML with standard indents and line breaks. Uncheck the Beautify checkbox to turn off this option. The content of XML will be formatted without indents and line breaks.

        Encoding: Select the type of encoding to use for the XML. The default depends on your platform.

      5. From the Rewrite Algorithm drop-down list, select a rewrite algorithm. In most cases, you should use the default.

        6. For more information about choosing rewrite algorithms for:

      6. Click OK to execute the query or statement. If you selected the:
        • To Window option, results appear in a separate window. Select File / Save as to save the results to a file. The following figure shows an XML document fragment, the result of a jXTransformer query.


          • Query Results window in Text view

        • To File option, the results are saved to the specified file.

      Browsing the Database

      To make sure that you know the correct columns and table names to retrieve from the database and use in your query, you can browse the database using a tool within the Builder named the DataDirect Technologies Database Browser (or Database Browser). A JDBC connection is required to browse the database. The Database Browser also allows you to customize the JDBC filter settings. See "Customizing JDBC Filter Settings" for instructions.

      Using the Database Browser

      1. To open the Database Browser, select Tools / Database Browser. The Database Browser appears.


        2. DataDirect Database Browser

        This example shows the Database Browser in a non-connected state. If you were already connected to the database when you opened the Database Browser, you would see the database tree in the left pane.

      2. If you are already connected to the database, continue with Step 3. Otherwise, make a JDBC connection. Right-click the Not connected node in the Database Browser, and select Connect to Database. The Open JDBC Connection dialog box appears.


        3. Open JDBC Connection dialog box

        The Connection URL option is selected by default. If you want to connect to the database using:

        Then, continue with the next step.

      3. Browse the database as needed. You can expand any node in the database tree by double-clicking that node or by single-clicking the + (plus sign) for that node. When you select an entity in the database tree, its properties appear in the right pane of the Database Browser as shown in the following example:.


      DataDirect Database Browser

      Customizing JDBC Filter Settings

      You can customize JDBC filter settings in the Database Browser to show specific database tables. For example, you may want to customize the filter settings to view database tables that begin with the characters "Emp."

      To customize the JDBC filter settings:

      1. In the Database Browser, select Tools / Options. The Options dialog box appears.


        2. Database Filter dialog box

      2. In the Name group, complete the following information:

        3. Show All Catalogs: Select this checkbox if you want to show all catalogs in the database.

        Schema: Using the % character as a wildcard, set a filter to only show schemas named with the characters you specify.

        Table: Using the % character as a wildcard, set a filter to only show tables named with the characters you specify.

        Column: Using the % character as a wildcard, set a filter to only show columns named with the characters you specify.

      3. In the Type group, select the type of information you want to see when you browse the database.
      4. When you are satisfied with your JDBC filter settings, click OK.

      Opening an XML DTD

      You can open an XML DTD in a separate window in the Builder for reference. For example, you may want to compare a DTD definition with the structure of an XML document.

      To open a DTD:

      1. Select File / Open DTD. The Open DTD dialog box appears.


        2. Open DTD dialog box

      2. In the Path field, select a path to the DTD you want to open from the drop-down list, type the path, or click the Browse (...) button to browse and select a DTD.
      3. Click OK. A window opens with the path and name of the DTD displayed in the top-level node. The following figure shows a jXTransformer query with a DTD opened.


      DTD View window

      Opening an XML Document

      You can open an XML document in a separate window in the Builder for reference. The Builder allows you to view the XML document in Tree view or Text view.

      To open an XML document:

      1. Select File / Open XML Document. The Open XML Document dialog box appears.


        2. Open XML Document dialog box

      2. In the Path field, select a path to the XML document you want to open from the drop-down list, type the path, or click the Browse (...) button to browse and select an XML document.
      3. Click OK. The XML document opens in a separate window with the path and name of the XML document in the title bar. The following figure shows a jXTransformer query with an XML document opened in the separate window.


      jXTransformer query with open XML document in separate window

      Generating DTDs and XML Schemas

      This section describes how to generate DTDs and XML schemas from a jXTransformer query using the Builder.

      Generating a DTD

      1. With a jXTransformer query open, select Project / Create DTD from query. If you are not connected to the database, you first must make a JDBC connection. The Open JDBC Connection dialog box appears.


        2. Open JDBC Connection dialog box

        The Connection URL option is selected by default. If you want to connect to the database using:

        Then, continue with the next step.

      2. Use the Create DTD dialog box to navigate to an existing directory or create a new directory for the DTD, and specify the filename of the DTD you want to create. Then, click Save.

      The DTD is created in the specified directory and appears in a separate window.


      DTD View dialog box

      Generating an XML Schema

      To generate an XML schema using the Builder, you must specify every namespace URI defined in your query. A separate XML schema will be created for each specified namespace.

      To generate an XML schema:

      1. With a jXTransformer query open, select Project / Create XML Schema from query. If you are not connected to the database, you first must make a JDBC connection. The Open JDBC Connection dialog box appears.


        2. Open JDBC Connection dialog box

        The Connection URL option is selected by default. If you want to connect to the database using:

        Then, continue with the next step.

      2. The Create XML Schema dialog box appears.


        3. Create XML Schema dialog box

        In the Output group, map namespace URIs or XML constructs that are not qualified by a namespace to XML schema files by completing the following information:

        Namespace URI: Type each namespace URI specified in your query as a separate entry. To generate an XML schema for XML constructs that are not qualified by a namespace, create a blank entry in this field, but make sure that you complete the Path field for that entry.

        Path: Type the corresponding full path and name of the file to which you want each XML schema saved, or click the Browse (...) button to navigate to a file.

      3. In the Options group, complete the following information:

        4. Beautify: This option is turned on by default. The Beautify option formats the content of the XML schema with standard indents and line breaks. Uncheck the Beautify checkbox to turn off this option. The content of the XML schema will be formatted without indents and line breaks.

        Encoding: Select the type of encoding to use for the XML schema. The default depends on your platform.

      4. Click OK. A separate XML schema is generated for each namespace entry and, if specified, any XML constructs that are not qualified by a namespace.

      Executing jXTransformer Write Statements

      You can use the Builder to test jXTransformer write statements by executing the jXTransformer write statement in the Text view of the jXTransformer Statement window.

      NOTE: You cannot use the Tree view of the jXTransformer Statement window to view or execute jXTransformer write statements.

      So that the Builder can locate the XML document referenced in the jXTransformer write statement, the path to the XML document must be specified in the write statement as a URL or the XML document must be in the same directory as the Builder tool. For example, let us look at the following jXTransformer Insert statement fragment:

      insert xml_document('Insert.xml', 1) 
  into Employees (EmpId, FirstName, LastName, Title, 
StartDate, HourlyRate, Resume)   
 ...

      In the previous example, the Builder would be unable to find Insert.xml unless that document existed in the Builder directory. The following example shows the same jXTransformer Insert statement fragment with a path to the XML document specified in a URL format:

      insert xml_document('file://C:\Program Files\DataDirect\
Connect for SQLXML\XML_documents\Insert.xml', 1) 
  into Employees (EmpId, FirstName, LastName, Title, 
StartDate, HourlyRate, Resume) 
 ...

      To execute a jXTransformer write statement:

      1. Create a Builder project for a jXTransformer write statement or open an existing Builder project for a jXTransformer write statement. See "Creating a Builder Project" and "Opening a Builder Project" for instructions.
      2. If you are creating a Builder project for a jXTransformer write statement, you can copy or import the jXTransformer write statement into the Text view of the jXTransformer Statement window. See "Importing a Query or Statement" for instructions. For example, the following figure shows an Insert statement in the jXTransformer statement window.


        3. jXTransformer Statement window with an jXTransformer Insert statement showing in Text view

      3. Select Project / Execute Statement to execute the jXTransformer write statement. If you are not connected to the database, you must make a JDBC connection. The Open JDBC Connection dialog box appears.


        4. Open JDBC Connection dialog box

        The Connection URL option is selected by default. If you want to connect to the database using:

        Once you are connected to the database, the Execute Statement dialog box appears confirming if the execution of the statement was successful. It also lists any update counts and prompts you to commit or roll back any database changes resulting from the write operation.



        In the preceding example, an array of update counts is returned. Each line of the array represents one database table that is updated. Each line returns one value for each of the nodes returned from the row pattern expression for that table. If more than one row is updated, a comma-separated list is returned. For example, 1,1,1 shows that three rows were updated in the database table.

      4. Commit or roll back database changes:
        • To commit database changes, click the Commit button.
        • To roll back database changes, click the Rollback button.

      A message appears confirming the operation you performed.

PREVNEXTINDEX