6 Syntax of jXTransformer Write Statements

---

This chapter describes the syntax of jXTransformer Write statements. jXTransformer write statements allow you to insert, update, and delete data, as explained in the following list:

• jXTransformer Insert statements insert data from an XML document into a relational database. New rows are created with the new data.

• jXTransformer Update statements update data in a relational database with data from an XML document.

• jXTransformer Delete statements delete data in a relational database. The rows that are deleted are specified by a Where clause in the jXTransformer Delete statement.

The following conventions are used to document the jXTransformer syntax:

• Bold type indicates keywords and tokens that must be entered as part of the write statement.

• Italic type indicates variables.

• Square brackets [ ] surround optional items.

• A vertical rule | indicates an OR separator to delineate items.

• Curly brackets { } surround items that can repeat zero or more times.

Insert Statement

jXTransformer Insert statements insert rows into a relational database based on column values extracted from an XML document. When you define a jXTransformer Insert statement, you specify:

1. The XML document from which to retrieve values to insert into the database table. For example:

insert xml_document('emp.xml')

• The database table and columns in which to insert the values. For example:

into Employees (EmpId, FirstName, LastName, Title, StartDate, HourlyRate, Resume)

• The nodes in the XML document from which values will be extracted. These locations are specified using XPath expressions. For example:

xml_row_pattern('/insert/employee')

• The XML element or attribute of the nodes specified in Step 3 from which to extract the values to insert in the database table. For example:

values(xml_xpath('@ID', 'Integer'),

xml_xpath('@FirstName'),

xml_xpath('@LastName'),

xml_xpath('@Title'),

xml_xpath('@StartDate', 'Timestamp'),

xml_xpath('@HourlyRate', 'Integer'),

xml_xpath('resume[1]/text()' )

)

Syntax

The syntax of a jXTransformer Insert statement is as follows:

Syntax

jxtr_insert ::= insert xml_document 

(('reference_to_xml' | ?)[, (ignore_whitespace | ?)] ) 

[xml_namespace (['prefix',]  'uri') 

{,xml_namespace (['prefix',]  'uri')}] 

{into table_name [(column_list)] 

  xml_row_pattern(('row_pattern_expression' | ? ))  

  jxtr_query_expression  

} 

      

where:

insert xml_document defines the XML document from which values are extracted and inserted into the database table. This construct is required.

reference_to_xml is a reference to the XML document from which the values for the Insert statement are being extracted. The value is the location of the XML document in URL format, for example, 'file://employee.xml'. This value must be surrounded by single quotes.

? is a parameter marker. You must set the values for parameter markers in your Java application using the jXTransformer API.

ignore_whitespace is 0 or 1. This parameter is optional. If set to 1 (the default), any leading or trailing whitespace that is part of the value of a node is deleted. If set to 0, the whitespace is not deleted. Whitespace is newline, carriage return, spaces, and tabs.

xml_namespace defines a namespace (prefix/URI mapping) for all XPath expressions used in the Insert statement. This construct is optional.

prefix is the namespace prefix that is used to qualify elements or attributes with the namespace URI as specified in the uri parameter. This value must be within single quotes and is optional. If you do not specify a prefix, the default namespace for the XPath expression is defined.

uri is the URI that identifies the namespace for the XPath expression to use. This value must be within single quotes and is required when you are defining a namespace for the XPath expression to use.

into defines the database table and columns in which to insert values. This construct is required.

table_name is the name and path of a database table. Refer to the SQL99 specification for more information.

column_list is an optional list of database table column names, separated by commas. It specifies the name and order of the columns that will store the values specified in jxtr_query_expression. If you omit column_list, jxtr_query_expression must provide values for all columns defined in the database table and they must be in the same order that the columns are defined in the table. Refer to the SQL99 specification for more information.

xml_row_pattern identifies the nodes in the XML input document from which values are extracted and inserted into the database table. This construct is required.

row_pattern_expression is an absolute XPath expression that returns a node set. The value must be surrounded by single quotes, for example, '//employee'. For each node in the returned node set, one row is inserted into the database table. Refer to the XPath specification at http://www.w3.org/TR/xpath for more information.

jxtr_query_expression is any valid SQL99 query expression with the difference that xml_xpath constructs can be used in the SQL99 query expression where the SQL99 syntax allows expressions. For example:

values ( 

  xml_xpath('@ID', 'Integer'), 

  xml_xpath('@FirstName') 

) 

This construct is required.

The syntax for an xml_xpath construct is:

xml_xpath(('xpath_expression' | ?)  

   [ [, ('java_sql_datatype' | ?) [, (scale | ?)] ]  

   [, (mixed_content_index | ?)] ] ) 

where:

xml_xpath identifies the XML element or attribute in the XML input document from which the value is extracted and inserted into the database table. This construct is optional.

xpath_expression is any valid XPath expression that is evaluated relative to each of the nodes returned from the xml_row_pattern construct. The value must be surrounded by single quotes, for example, '@ID'. These expressions, when specified, define the column values being inserted into the database. Refer to the XPath specification at http://www.w3.org/TR/xpath for more information.

java_sql_datatype is one of the field string names or int values from java.sql.Types. When using field string names, the value must be surrounded by single quotes, for example, 'INTEGER'. If you do not specify a value, CHAR is used.

scale is an integer that specifies the number of digits after the decimal point; it is only valid for DECIMAL and NUMERIC java.sql.Types. For all other types, this value is ignored.

mixed_content_index is an integer that specifies the ordinal position of one value in a set of returned values to use for the Insert statement. The default is 0, which means to concatenate all the returned values and use that concatenated value for the Insert statement. Typically, you need only specify a value for this argument when the element referred to in the XPath expression has mixed content and you want to insert only one value in the database table.

Example

The following example inserts new rows into three tables:

• Employees table: EmpID, FirstName, LastName, Title, StartDate, HourlyRate, and Resume columns

• EmpBenefits table: BenefitId, EmpId, Amount, and StartDate columns

• Assignments table: ProjId, EmpId, and Task columns

The values for the columns are extracted from the XML document emp.xml, shown next:

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

<insert> 

  <employee ID="21" FirstName="Anne" LastName="Dodsworth" 

    Title="Miss" StartDate="2001-10-24" HourlyRate="115"> 

    <resume><![CDATA[

      <a href='http://www.xesdemo/resume/21.htm'>

       A. Dodsworth</a>]]></resume> 

    <benefits> 

      <benefit ID="1" Amount="1" 

      <benefit ID="2" Amount="175" StartDate="2001-11-01"/> 

    </benefits> 

    <projects> 

      <project ID="8"> 

        <task>Analysis</task> 

        <task>Development</task> 

      </project> 

      <project ID="9"> 

        <task>Analysis</task> 

      </project> 

    </projects> 

  </employee> 

</insert> 

Insert statement:

insert xml_document('emp.xml', 1) 

  into Employees (EmpId, FirstName, LastName, Title,

    StartDate, HourlyRate, Resume) 

    xml_row_pattern('/insert/employee') 

    values(xml_xpath('@ID', 'Integer'), 

           xml_xpath('@FirstName'), 

           xml_xpath('@LastName'), 

           xml_xpath('@Title'), 

           xml_xpath('@StartDate', 'Timestamp'), 

           xml_xpath('@HourlyRate', 'Integer'), 

           xml_xpath('resume[1]/text()' ) 

          ) 

  into EmpBenefits (BenefitId, EmpId, Amount, StartDate) 

    xml_row_pattern('/insert/employee/benefits/benefit') 

    values(xml_xpath('@ID', 'Integer'), 

           xml_xpath('../../@ID', 'Integer'), 

           xml_xpath('@Amount', 'Integer'), 

           xml_xpath('@StartDate', 'Timestamp') 

          ) 

  into Assignments (ProjId, EmpId, Task) 

  xml_row_pattern('/insert/employee/projects/project/task') 

    values(xml_xpath('../@ID', 'Integer'), 

           xml_xpath('../../../@ID', 'Integer'), 

           xml_xpath('text()') 

          ) 

Results:

One new row inserted into the Employees table:

EmpId	LastName	FirstName	Title	StartDate	EndDate	HourlyRate	Resume
21	Dodsworth	Anne	Miss	2001-10-24		115	<a href= 'http://www.xesdemo/ resume21.htm'> A.Dodsworth</a>

Two new rows inserted into the EmpBenefits table:

EmpId	BenefitId	StartDate	EndDate	Amount
21	1	2001-10-24		1
21	2	2001-22-01		175

Three new rows inserted into the Assignments table:

EmpId	ProjId	Task	StartDate	EndDate	TimeUsed	EstimatedDuration
21	8	Analysis
21	8	Development
21	9	Analysis

Update Statement

jXTransformer Update statements update column values in a relational database with new column values extracted from an XML document. When you define a JXTransformer Update statement, you specify:

1. The XML document from which to retrieve new values to use to update the database table. For example:

update xml_document('emp.xml')

• The database table to update. For example:

Employees

• The nodes in the XML document from which values will be extracted. These locations are specified using XPath expressions. For example:

xml_row_pattern('/update/employee')

• The database column to update and the XML element or attribute of the nodes specified in Step 3 from which to extract the new value for the column. For example:

set HourlyRate = xml_xpath('@HourlyRate','Integer')

• The database columns to use to identify which rows to update and the XML elements or attributes of the node from which to retrieve the value that identifies the rows to update. For example:

WHERE EmpId = xml_xpath('@ID','Integer')

NOTE: It is possible to have multiple parts in the Where clause. For example:

WHERE EmpId = xml_xpath('@ID','Integer') and

StartDate < xml_xpath('@StartDate', 'Timestamp')

Syntax

The syntax of a jXTransformer Update statement is as follows:

Syntax

jxtr_update ::= update xml_document

(('reference_to_xml' | ?)[, (ignore_whitespace | ?)] ) 

[xml_namespace (['prefix',]  'uri') 

{,xml_namespace (['prefix',]  'uri')}] 

{table_name xml_row_pattern(

  ('row_pattern_expression' | ? ))  

  {set jxtr_set_clause_list} 

  where jxtr_search_condition 

} 

where:

update xml_document defines the XML document from which values are extracted and updated in the database table. This construct is required.

reference_to_xml is a reference to the XML document from which the values for the Update statement are being extracted. The value is the location of the XML document in URL format, for example, 'file://employee.xml'. This value must be surrounded by single quotes.

? is a parameter marker. You must set the values for parameter markers in your Java application using the jXTransformer API.

ignore_whitespace is 0 or 1 and is optional. If set to 1 (the default), any leading or trailing whitespace that is part of the value of a node is deleted. If set to 0, the whitespace is not deleted. Whitespace is newline, carriage return, spaces, and tabs.

xml_namespace defines a namespace (prefix/URI mapping) for all XPath expressions used in the Update statement. This construct is optional.

prefix is the namespace prefix that is used to qualify elements or attributes with the namespace URI as specified in the uri parameter. This value must be within single quotes and is optional. If you do not specify a prefix, the default namespace for the XPath expression is defined.

uri is the URI that identifies the namespace for the XPath expression to use. This value must be within single quotes and is required when you are defining a namespace for the XPath expression to use.

table_name is the name of a database table. This construct is required. Refer to the SQL99 specification for more information.

xml_row_pattern defines the XML nodes from which values will be used to update the database table. This construct is required.

row_pattern_expression is an absolute XPath expression that returns a node set. The value must be surrounded by single quotes, for example, '//employee'. Refer to the XPath specification at http://www.w3.org/TR/xpath for more information.

set defines the database columns to update and the XML document nodes from which to retrieve the new value. This construct is required.

jxtr_set_clause_list is any valid SQL99 Set clause list with the addition that xml_xpath constructs can be used in the SQL99 Set clause list where the SQL99 syntax allows expressions. For example:

set HourlyRate = xml_xpath('@HourlyRate','Integer')

The syntax for an xml_xpath construct is:

xml_xpath(('xpath_expression' | ?)

[ [, ('java_sql_datatype' | ?) [, (scale | ?)] ]

[, (mixed_content_index | ?)] ] )

where:

xml_xpath defines the XML element or attribute from which to retrieve the value to be used to update the database table. This construct is optional.

xpath_expression is any valid XPath expression. The value must be surrounded by single quotes, for example, '@ID'. These expressions, when specified, define either the new values to use to update the columns (jxtr_set_clause_list) or the column values being updated in the database (jxtr_search_condition). Refer to the XPath specification at http://www.w3.org/TR/xpath for more information.

java_sql_datatype is one of the field string names or int values from java.sql.Types. When using field string names, the value must be surrounded by single quotes, for example, 'INTEGER'. If you do not specify a value, CHAR is used.

scale is an integer that specifies the number of digits after the decimal point; it is only valid for DECIMAL and NUMERIC java.sql.Types. For all other types, this value is ignored.

mixed_content_index is an integer that specifies the ordinal position of one value in a set of returned values to use for the Update statement. The default is 0, which means to concatenate all of the returned values and use that value for the Update statement. Typically, you need only specify a value for this argument when the element referred to in the XPath expression has mixed content and you want to update only one value in the database table.

where defines the rows in the database table to update. This construct is required.

jxtr_search_condition is any valid SQL99 search condition with the addition that xml_xpath constructs can be used in the SQL99 search condition where the SQL99 syntax allows expressions. For example:

WHERE EmpId = xml_xpath('@ID','Integer')

Example

The following example updates one row in one database table, Employees.

The XML document emp.xml, shown next, specifies the column value that the Update statement uses to identify which row in the relational database table to update and the new value to use for the column to be updated. The row with an EmpId of 21 is the row to be updated, and the HourlyRate column is the column to be updated to a value of 120.

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

<update> 

  <employee ID="21" HourlyRate="120"> 

  </employee> 

</update> 

Update statement:

Update xml_document('emp.xml') 

  Employees xml_row_pattern('/update/employee') 

    set HourlyRate = xml_xpath('@HourlyRate','Integer') 

    WHERE EmpId = xml_xpath('@ID','Integer') 

Delete Statement

jXTransformer Delete statements delete rows from a relational database table based on column values extracted from an XML document. When you define a jXTransformer Delete statement, you specify:

1. The XML document from which to retrieve values that identify the rows in the database table to delete. For example:

delete xml_document('emp.xml')

• The database table from which to delete rows. For example:

FROM Employees

• The nodes of the XML document from which the values that identify which rows to delete are extracted. These locations are specified using XPath expressions. For example:

xml_row_pattern('/update/employee')

• The database columns to use to identify the rows to delete and the XML element or attribute of the nodes specified in Step 3 from which to extract the value that identifies the rows to delete. For example:

WHERE EmpId = xml_xpath('@ID','Integer')

Syntax

The syntax of a jXTransformer Delete statement is as follows:

jxtr_delete ::= delete xml_document 

(('reference_to_xml' | ?)[, (ignore_whitespace | ?)] ) 

[xml_namespace (['prefix',]  'uri') 

{,xml_namespace (['prefix',]  'uri')}] 

{from table_name 

  xml_row_pattern(('row_pattern_expression' | ? ))  

    where jxtr_search_condition 

} 

      

where:

delete xml_document defines the XML document from which values are extracted to identify which rows to delete from the database table. This construct is required.

reference_to_xml is a reference to the XML document from which the values for the Delete statement are being extracted. The value is the location of the XML document in URL format, for example, 'file://employee.xml'. This value must be surrounded by single quotes.

? is a parameter marker. You must set the values for parameter markers in your Java application using the jXTransformer API.

ignore_whitespace is 0 or 1. This parameter is optional. If set to 1 (the default), any leading or trailing whitespace that is part of the value of a node is deleted. If set to 0, the whitespace is not deleted. Whitespace is newline, carriage return, spaces, and tabs.

xml_namespace defines a namespace (prefix/URI mapping) for all XPath expressions used in the Delete statement.

prefix is the namespace prefix that will be used to qualify elements or attributes with the namespace URI as specified in the uri parameter. This value must be within single quotes and is optional. If you do not specify a prefix, the default namespace for the XPath expression is defined.

uri is the URI that identifies the namespace for the XPath expression to use. This value must be within single quotes and is required when you are defining a namespace for the XPath expression to use.

from defines the database table on which the delete operation will take place. This construct is required.

table_name is a simple database table name or full pathname. Refer to the SQL99 specification for more information.

xml_row_pattern defines the XML nodes from which values are used to identify the row to delete in the database table.

row_pattern_expression is an absolute XPath expression that returns a node set. The value must be surrounded by single quotes, for example, '//employee'. Refer to the XPath specification at http://www.w3.org/TR/xpath for more information.

where defines the rows in the database table to delete. This construct is required.

jxtr_search_condition is any valid SQL99 search condition with the addition that xml_xpath constructs can be used in the SQL99 search condition where the SQL99 syntax allows expressions. For example:

WHERE EmpId = xml_xpath('@ID','Integer')

The syntax for the xml_xpath construct is:

xml_xpath(('xpath_expression' | ?)

[ [, ('java_sql_datatype' | ?) [, (scale | ?)] ]

[, (mixed_content_index | ?)] ] )

where:

xml_xpath defines the XML element or attribute from which to retrieve the value to be used to identify the rows to delete from the database table. This construct is optional.

xpath_expression is any valid XPath expression. The value must be surrounded by single quotes, for example, '@ID'. These expressions, when specified, define the column values of the rows being deleted from the database. Refer to the XPath specification at http://www.w3.org/TR/xpath for more information.

java_sql_datatype is one of the field string names or int values from java.sql.Types. When using field string names, the value must be surrounded by single quotes, for example, 'INTEGER'. If you do not specify a value, CHAR is used.

scale is an integer that specifies the number of digits after the decimal point; it is only valid for DECIMAL and NUMERIC java.sql.Types. For all other types, this value is ignored.

mixed_content_index is an integer that specifies the ordinal position of one value in a set of returned values to use for the Delete statement. The default is 0, which means to concatenate all the returned values and use that value for the Delete statement. Typically, you need only specify a value for this argument when the element referred to in the XPath expression has mixed content and you want to use only one of the mixed content parts in the Delete statement's Where clause.

Example

The following example deletes rows from three different tables-Assignments, EmpBenefits, and Employees. The rows that are deleted contain the value 21 in the EmpId column of these tables. The example uses a parameter marker for the reference to the XML document that is used in this delete transaction. The value for this marker must be set in the Java application that executes the jXTransformer Delete statement. Also, this example uses an xml_namespace constructor to define a prefix and URI mapping for all XPath expressions used in the Delete statement.

The XML document delete.xml, shown next, specifies the column value that the Delete statement uses to identify which rows in the relational database table to delete.

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

<root xmlns:emp="http://www.jxtrdemo/delete"> 

  <emp:employee ID="21"/> 

</root> 

Delete statement:

delete xml_document(?) 

  xml_namespace('emp','http://www.jxtrdemo/delete') 

  FROM Assignments 

    xml_row_pattern('/root/emp:employee') 

    WHERE EmpId = xml_xpath('@ID','Integer') 

  FROM EmpBenefits 

    xml_row_pattern('/root/emp:employee') 

    WHERE EmpId = xml_xpath('@ID','Integer') 

  FROM Employees 

    xml_row_pattern('/root/emp:employee') 

    WHERE EmpId = xml_xpath('@ID','Integer') 

Executing jXTransformer Write Statements

jXTransformer Insert, Update, and Delete statements are executed using a JXTRUpdate object, which is an object defined in the jXTransformer API. See Chapter 8 "Using the jXTransformer API" for more information about using the jXTransformer API.