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>