In Transact-SQL, an expression subquery is a parenthesized subquery. It has a single column, the value of which is the expression subquery result, and must return a single row. You can use an expression subquery almost anywhere you can use an expression. For more information about subqueries, see the Transact-SQL® User’s Guide.
The for xml subqueries feature allows you to use any subquery containing a for xml clause as an expression subquery.
subquery ::= select [all | distinct ] select_list (select select_list [from table_reference [, table_reference]... ] [where search_conditions] [group by aggregate_free_expression [aggregate_free_expression]...] [having search_conditions] [for_xml_clause]) for_xml_clause:: = See “for xml schema and for xml all” on page 64 table_reference::= table_view_name |ANSI_join | derived_table table_view_name::= See SELECT in Vol. 2, “Commands, in the “Reference Manual” ANSI_join::= See SELECT in Vol. 2, “Commands,” in the “Reference Manual” derived_table::= (subquery) as table_name
A select command containing a for xml clause generates an XML document that represents the results of the select statement, and returns that XML document as a result set, with a single row and a single column. You can access that result set using normal techniques for processing result sets.
For a general description of the for xml clause and its option_string, see “for xml clause”. For a description of extensions to the for xml clause that support the schema keyword and the return clause, see “for xml schema and for xml all”.
A for xml subquery is a subquery that contains a for xml clause.
You can use a for xml subquery as an expression subquery, though there are some differences between them; for example, the following restrictions apply to ordinary expression subqueries, but not to for xml subqueries:
No multiple items in the select list
No text and image columns in the select list
No group by or having clauses
You cannot specify a for xml subquery within a for xml select or within another for xml subquery.
You cannot use a for xml subquery in these commands:
for xml select
create view
declare cursor
select into
as a quantified predicate subquery, such as any/all, in/not in, exists/not exists
A for xml subquery cannot be a correlated subquery. For more information on correlated subqueries, see the Transact-SQL User’s Guide.
A for xml clause that returns a text or unitext datatype cannot be used in a nested scalar subquery.
The datatype of a for xml subquery is specified by the returns clause of the for_xml_clause. If a returns clause specifies no datatype, the default datatype is text.
Exceptions are the same as those specified for the for_xml_clause.
If the returns clause specifies a datatype to which you cannot convert the result of the subquery, an exception is raised: Result cannot be converted to the specified datatype.
Example 1 A for xml subquery returns the XML document as a string value, which you can assign to a string column or variable, or pass as an argument to a stored procedure or built-in function. For example:
declare @doc varchar(16384) set @doc = (select * from systypes for xml returns varchar(16384)) select @doc --------------
Example 2 To pass the result of a for xml subquery as a string argument, enter:
select xmlextract('//row[usertype = 18]',
(select * from systypes for xml))
------------
Example 3 To specify a for xml subquery as a value in insert or update:
create table docs_xml(id integer, doc_xml text) insert into docs_xml select(1, (select * from systypes for xml) --------
update docs_xml set doc_xml = (select * from sysobjects for xml) where id = 1 ------------
