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_clause ::=
     for xml [schema | all] [option option_string] [returns_clause]
 option_string ::= basic_string_expression
 returns_clause ::=
     returns { char [(integer)] | varchar [(integer)] 
     |unichar [(integer)] | univarchar [(integer)]
     |text | unitext | java.lang.String}

See “option_strings: general format” for more information about option strings.

See Chapter 6, “XML Support for I18N” for more information on using for xml with I18N data.

Description

The for xml clause is a new clause in SQL select statements. The syntax shown above for select includes all of the clauses, including the for xml clause.

The syntax and description of the other select statement clauses are in Sybase Adaptive Server Reference Manual, Volume 2: “Commands.”

The for xml clause supports the java.lang.string datatype, represented as string. Any other Java type is represented as objectID.

For a description of for xml schema and for xml all, see “for xml schema and for xml all”.

The variants of the for xml clause are as follows:
1. 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.
2. A for xml schema select command or subquery has a for_xml_clause that specifies schema.
3. A for xml all select command or subquery has a for_xml_clause that specifies all.
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 returns clause specifies the datatype of the XML document generated by a for xml query or subquery. If no datatype is specified by the returns clause, the default is text.
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 value of that 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"
```
The date and time fields in a datetime value in the results of a for xml query are separated by the delimiter 'T' (letter T) as now specified in the ANSI SQL-XML standard. Without this format, validation fails with standard XML parsers.

For example, if you execute this query in Adaptive Server 12.5.2, the results are:

select getdate() for xml

<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
< row > 
< C1 > 2008-05-30 11:42:19 < /C1 > 
< /row > 
< /resultset >

But in Adaptive Server 15.0.2, the results from the same query are:

select getdate() for xml

<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
< row > 
< C1 > 2008-05-30T11:41:42 < /C1 > 
< /row > 
< /resultset >

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 l 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
----------------
<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>