Chapter 3 XML Language and XML Query Language

XML Services in Adaptive Server Enterprise

Chapter 4 XML Mapping Functions

Chapter 4

XML Mapping Functions

This chapter describes the XML mapping functions in detail, and provides examples for them.

for xml clause

Specifies a SQL select statement that returns an XML representation of the result set.

Syntax

select ::=
     select [ all | distinct ] select_list
     [into_clause ]
     [where_clause ]
     [group_by_clause ]
     [having_clause ]
     [order_by_clause ]
     [compute_clause ]
     [read_only_clause ]
     [isolation_clause ]
     [browse_clause ]
     [plan_clause]for_xml_clause::=
    |for xml[option option_string]
 option_string::=basic_character_expression

For more information about option strings, see option_strings: general format.

Description

for_xml_clause is a new clause in SQL select statements. The syntax shown above for select includes all of the clauses, including for_xml_clause.
for xml clause does not support the following datatypes: image, text, binary, varinary, timestamp, unichar, univarchar, java.lang.String(Abstract types)
The syntax and description of the other clauses are in Sybase Adaptive Server Reference Manual, Volume 2: "Commands."
If a select statement specifies a for_xml_clause, refer to the select statement itself as basic select, and the select statement with a for_xml_select as for_xml select. For example, in the statement
```
select 1, 2 for xml
```
the basic select is select 1, 2, and the for_xml_select is select 1, 2 for xml.
A for_xml_select statement cannot include an into_clause, compute_clause, read_only_clause, isolation_clause, browse_clause, or plan_clause.
for_xml_select cannot be specified in the commands create view, declare cursor, subquery, or execute command.
for_xml_select cannot be joined in a union, but it can contain unions. For instance, this statement is allowed:
```
select * from T union select * from U for xml
```
But this statement is not allowed:
```
select * from T for xml union select * from U
```
The value of for_xml_select is an XML representation of the result of the basic select statement. The format of that XML document is the SQLX format described in Chapter 5, "XML Mappings."
The result set that a for_xml_select statement returns depends on the incremental option:
- incremental = no returns a result set containing a single row and a single column. The column datatype is text. The value of that text column is the SQLX-XML representation of the result of the basic select statement. This is the default option.
- incremental = yes returns a result set containing a row for each row of the basic select statement. If the root option specifies yes (the default option), an initial row specifies the opening XML root element, and a final row specifies the closing XML root element.
  For example, these select statements return two, one, two, and four rows, respectively:
```
select 11, 12 union select 21, 22
select 11, 12 union select 21, 22 for xml
select 11, 12 union select 21, 22 
     for xml option "incremental=yes root=no"
select 11, 12 union select 21, 22 
     for xml option "incremental=yes root=yes"
```

Options

The general format of the option_string is specified in option_strings: general format. The options for the for_xml_clause are specified in "SQLX Options."

Exceptions

Any SQL exception raised during execution of the basic select statement is raised by the for_xml select. For example, both of the following statements raise a zero divide exception:

select 1/0
select 1/0 for xml

Example

The for_xml clause:

select pub_id, pub_name 
from pubs2.dbo.publishers
for xml
go


 <resultset
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <row>
   <pub_id>0736</pub_id>
   <pub_name>NewAgeBooks</pub_name>
 </row>

 <row>
   <pub_id>0877</pub_id>
   <pub_name>Binnet & Hardley</pub_name>
 </row>

 <row>
   <pub_id>1389</pub_id>
   <pub_name>Algodata Infosystems</pub_name>
 </row>

 </resultset>

forxmlj, forxmldtdj, forxmlschemaj, forxmlallj

The functions in this section are Java-based, and you must install them in your server before you can use them. For instructions see Appendix E, "The Java-Based XQL Processor. ".

The Java-based forxml functions map the result set of a SQL query to a SQLX-XML schema, result set document, or both. The SQL query is specified as a character string, containing an arbitrary SQL query expression. When xmlextract and xmltest process the output SQLX-XML documents that forxmlj functions generate, they provide an XML view of the SQL data that is represented by the SQLX-XML documents.

forxmlj is a functional form of the mapping provided by the for xml clause of the select statement. The differences are:

In some contexts, such as function arguments, update statement set clauses, and insert statement value lists, you can use the forxmlj function but not a select statement with for xml.
A select statement with a for xml clause returns the result as text. The forxmlj function returns the result as java.lang.String.
A select statement with a for xml clause returns either a single row or multiple rows, depending on the incremental option. The forxmlj function returns a single result.

Syntax

forxmljfunction ::=
      forxmlj(sql_query_expression, option_string)
      | forxmldtdj(sql_query_expression, option_string)
      | forxmlschemaj(sql_query_expression, option_string)
 forxmlallj_procedure::=
     execute forxmlallj
        sql_query_expression, option_string
        rs_target_out, schema_target_out, dtd_target_out
 sql_query_expression::=basic_string_expression
 option_string::=basic_string_expression

Description

A basic_string_expression is a sql_query_expression whose datatype is character, varchar, or java.lang.String.
If any parameter of forxmlj is null, then the result of the call is null.
If the sql_query_expression is an all-blank or empty string, then the result of the call is an empty string.
The sql_query_expression must contain a valid SQL select statement, which can include a from clause, where clause, group by clause, having clause, and order by clause. It cannot include an into clause, compute clause, read_only clause, isolation clause, browse clause, or plan clause.
forxmlj evaluates the sql_query_expression and returns a SQLX-XML document containing the result set, formatted as a SQLX result set.
forxmldtdj evaluates the sql_query_expression, and returns an XML DTD describing the SQLX-XML result set for that query.
forxmlschemaj evaluates the sql_query_expression, and returns a SQLX-XML schema describing the SQL-XML result set for that query.
The forxmlallj procedure evaluates the sql_query_expression, and returns a SQLX-XML result set, schema, and DTD for that query.

For a description of the SQLX-XML representation of SQL result sets, see Chapter 5, "XML Mappings."

Options

The general format of the option_string is specified in option_strings: general format. The options for the for xml clause are specified in Chapter 5, "XML Mappings."

Exceptions

Any SQL exception raised during execution of the sql_query_expression is raised by the forxmlj function.

Examples

The forxmlj function:

set stringsize 16384 
select forxmlj  
      ("select pub_id, pub_name 
         from pubs2.dbo.publishers", "")  
go

 <resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
 <row>
   <pub_id>0736</pub_id>
   <pub_name>New AgeBooks</pub_name>
 </row>

 <row>
   <pub_id>0877</pub_id>
   <pub_name>Binnet & Hardley</pub_name>
 </row>

 <row>
   <pub_id>1389</pub_id>
   <pub_name>Algodata Infosystems</pub_name>
 </row>

 </resultset>

The forxmldtdj function:

set stringsize 16384 
select forxmldtdj    
      ("select pub_id, pub_name 
         from pubs2.dbo.publishers",     
      "tablename=extract null=omit") 
go

 <!ELEMENT extract (row*)>
 <!ELEMENT row (pub_id, pub_name?)>
 <!ELEMENT pub_id (#PCDATA)>
 <!ELEMENT pub_name (#PCDATA)>

The forxmlschemaj function:

set stringsize 16384
select forxmlschemaj
     ("select pub_id, pub_name 
        from pubs2.dbo.publishers",
     "tablename=extract null=omit")

 <xsd:schema
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:sqlxml=
   "http://www.iso-standards.org/mra/9075/sqlx">

 <xsd:simpleType name="CHAR_4"> 
   <xsd:restriction base="xsd:string">
      <xsd:length value="4"/>
   </xsd:restriction>   
 </xsd:simpleType>

 <xsd:simpleType name="VARCHAR_40">
   <xsd:restriction base="xsd:string">
      <xsd:length value="40"/>
   </xsd:restriction>
 </xsd:simpleType>

 <xsd:complexType name="RowType.extract">
   <xsd:sequence>
       <xsd:element name="pub_id" type="CHAR_4"
          minOccurs="0" MaxOccurs="1"/>
        <xsd:element name="pub_name" type="VARCHAR_40"
          minOccurs="0" maxOccurs="1"/>
   </xsd:sequence>
 </xsd:complexType>   

 <xsd:complexType name="TableType.extract">
   <xsd:sequence>
       <xsd:element name="row" type="RowType.extract"
         minOccurs="0" maxOccurs="unbounded"/>
   </xsd:sequence>
 </xsd:complexType>   

 <xsd:element name="extract"type="TableType.extract"/>
 </xsd:schema>

The forxmlallj procedure:

set stringsize 16384 
declare @rs varchar(16384) 
declare @schema varchar(16384) 
declare @dtd varchar(16384) 
execute forxmlallj  
  "select pub_id, pub_name from pubs2.dbo.publishers",
  "name=extract  null=attribute",  
  @rs out, @schema    out, @dtd out 
go

 <extract 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <row>
   <pub_id>0736</pub_id>
   <pub_name>New Age Books</pub_name>
 </row>

 <row>
   <pub_id>0877</pub_id>
   <pub_name>Binnet & Hardley</pub_name>
 </row>

 <row>
   <pub_id>1389</pub_id>
   <pub_name>Algodata Infosystems</pub_name>
 </row>
 </extract>

 <xsd:schema
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:sqlxml=
   "http://www.iso-standards.org/mra/9075/sqlx">
 <xsd:simpleType name="CHAR_4">
   <xsd:restriction base="xsd:string">
      <xsd:length value="4"/>
   </xsd:restriction>
 </xsd:simpleType>

 <xsd:simpleType name="VARCHAR_40">
   <xsd:restriction base="xsd:string">
     <xsd:length value="40"/>
   </xsd:restriction>
 </xsd:simpleType> 

 <xsd:complexType name="RowType.extract">
   <xsd:sequence>
     <xsd:element name="pub_id" type="CHAR_4"
          nillable="true" />
     <xsd:element name="pub_name" type="VARCHAR_40"
          nillable="true" />
   </xsd:sequence> 
 </xsd:complexType> 

 <xsd:complexType name="TableType.extract">
   <xsd:sequence>
      <xsd:element name="row" type="RowType.extract"
         minOccurs="0" maxOccurs="unbounded"/>
   </xsd:sequence>
 </xsd:complexType>
 <xsd:elementname="extract"type="TableType.extract">
 </xsd:schema>

 <!ELEMENT extract (row*)>
 <!ELEMENT row (pub_id, pub_name)>
 <!ELEMENT pub_id (#PCDATA)>
 <!ELEMENT pub_name (#PCDATA)>

forsqlcreatej, forsqlinsertj, forsqlscriptj

The functions in this section are Java-based, and you must install them in your server before you can use them. For instructions, see Appendix E, "The Java-Based XQL Processor. ".

The Java-based forsql functions map SQLX-XML schema and SQLX-XML result set documents to a SQL script.

The SQLX-XML schema and result set documents are of the form generated by the forxmlj functions.
The forsqlschemaj function maps a SQLX-XML schema to a SQL create command, and creates a table suitable for the data described by the SQLX-XML schema.
The forxmlinsertj function maps a SQLX-XML result set to a sequence of SQL insert commands, and re-creates the data described by the SQLX-XML result set.
The forxmlscriptj function maps both a SQLX-XML schema and a SQLX-XML result set to a SQL create command and creates a table suitable for the data described by the SQLX-XML schema, and a sequence of SQL insert commands that re-create the data described by the SQLX-XML result set.

Syntax

sqlx_to_sql_script_function ::=
      forsqlcreatej(sqlx_schema, option_string)
      | forsqlinsertj(sqlx_resultset, option_string)
      |forsqlscriptj(sqlx_schema, sqlx_resultset, option_string)
 sqlx_schema::=basic_string_expression
 sqlx_resultset::=basic_string_expression
 option_string::=basic_string_expression

Description

A basic_string_expression is a sql_query_expression whose datatype is character, varchar, or java.lang.String.
If any parameter of forsqlcreatej, forsqlschemaj, or forsqlscriptj is null, then the result of the call is null.
If sqlx_schema or sqlx_resultset is an all-blank or empty string, then the result of the call is an empty string.
sqlx_schema must contain a valid XML document that contains a SQLX-XML schema.
sqlx_resultset must contain a valid XML document that contains a SQLX-XML result set.
forsqlcreatej generates a SQL create command to create a SQL table suitable for the data described by sqlx_schema.
forsqlinsertj generates a sequence of SQL insert commands to populate a SQL table with the data of sqlx_resultset.
Because this function operates on a SQLX-XML result set without a corresponding schema, the generated insert commands assume that all of the data is varchar.
forsqlscriptj generates a SQL create and a sequence of SQL insert commands to populate a SQL table with the data of the sqlx_resultset.
Because this function operates on both a SQLX-XML schema and result set, create specifies the column datatypes of sqlx_schema, and the insert commands assume those datatypes.
The scripts generated use quoted identifiers for all identifiers. This does not affect subsequent reference to any regular identifiers.

Options

The general format of the option_string is described in option_strings: general format.

The forsqlcreatej, forsqlinsertj, and forsqlscripj functions support the following option, described in the "Exceptions" section, below.

xmlerror={exception | null | message}

Exceptions

If the value of sqlx_schema or sqlx_resultset is not valid XML:

If the explicit or default options specify:
```
xmlerror=exception
```
an exception is raised:
```
invalid XML data
```
If the explicit or default options specify:
```
xmlerror=null
```
a null value is returned.
If the explicit or default options specify:
```
xmlerror=message
```
a character string containing an XML element containing the exception message is returned. This value is in the form of a SQL comment, so the returned value is valid SQL.

Examples

The forsqlcreatej function:

set stringsize 16384
declare @schema varchar(16384) 
select @schema = forxmlschemaj(  
   "select pub_id, pub_name from pubs2.dbo.publishers",
   "tablename=extract  null=attribute") 
select forsqlcreatej(@schema, "") 
go
CREATE TABLE "extract"(      
   "pub_id" CHAR(4) null,       
   "pub_name" VARCHAR(40) null )

The forsqlinsertj function:

set stringsize 16384 
declare @rs varchar(16384) 
select @rs = forxmlj(   
   "select pub_id, pub_name from pubs2.dbo.publishers")
select forsqlinsertj(@rs, "")
go

   --Begin table "resultset"
insert into "resultset"   
   ("pub_id", "pub_name")    
    values ( '0736', 'New Age Books') 
insert into "resultset"   
   ("pub_id", "pub_name")    
    values ( '0877', 'Binnet & Hardley') 
insert into "resultset"   
   ("pub_id", "pub_name")    
    values ( '1389', 'Algodata Infosystems') 
--End table "resultset"

The forsqlscriptj function:

set stringsize 16384
declare @rs varchar(16384)
declare @schema varchar(16384)
declare @dtd varchar(16384)
execute forxmlallj   
   "select pub_id, pub_name from pubs2.dbo.publishers",
   "tablename=extract  null=attribute",     
    @rs out, @schema out, @dtd out
declare @script varchar(16384)

select @script = forsqlscriptj(@schema, @rs, "") 
select @script
execute ("set quoted_identifier on " + @script )
execute ("select pub_id, pub_name from extract")
execute ("drop table extract")
go
(return status = 0)

Return parameters:

*****Values of @rs, @schema, and @dtd omitted********
(1 row affected)
(1 row affected) 

CREATE TABLE "extract"(      
   "pub_id" CHAR(4) null,      
    "pub_name" VARCHAR(40) null)

--Begin table "extract"
insert into "extract"   
   ("pub_id", "pub_name")    
    values ( '0736', 'New Age Books') 
insert into "extract"   
   ("pub_id", "pub_name")    
    values ( '0877', 'Binnet & Hardley') 
insert into "extract"   
   ("pub_id", "pub_name")    
    values ( '1389', 'Algodata Infosystems') 
--End table "extract" 

(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected) 

pub_id pub_name
------ -------------------
1)    New Age Books                            
2)    Binnet & Hardley                         
3)    Algodata Infosystems                     

(3 rows affected)


	Expand Search