for xml schema and for xml all

This section describes additional forms of the for xml clause. You can generate an XML schema, an XML schema and XML DTD, or the XML data document.

Description

The select statement or subquery with a for xml schema clause produces an XML document, which describes the same SQLX XML result set that would be generated by the select statement if it contained the for xml clause without the schema predicate.

The result of this for xml subquery is an xml value:

(subquery for xml schema option option_string)

A select statement or subquery with a for xml all clause produces an XML document that contains the SQLX result set, the XML schema, and the XML DTD that describes that result set. These are contained in a single XML document with the following elements:
- <multiple-results>—The root element
- <multiple-results-item type="result-set">—an element containing:
  
  <multiple-results-item-dtd>—the DTD for the result set
  
  <multiple-result-item-schema>—the XML schema for the result set
  
  <multiple-result-item-data>—the result set

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

The exceptions to extensions are the same as those specified in “SQLX options”.

Examples—Usage

These examples show uses of for xml schema and for xml all.

Example 1

Example 1 In this example, a for xml all subquery returns

the XML schema
the XML schema and XML DTD
the result set as an XML document

These are all returned in a string value, which you can either assign to a string column or variable, or pass as a string argument to a stored procedure or function.

declare @doc varchar(16384)
set @doc = (select * from systypes for xml all returns varchar(16384))
select @doc
-----------

Example 2

Example 2 This example passes the result of a for xml schema subquery as a string argument:

select xmlextract('//row[usertype=18]'
   (select * from systypes for xml all))
---------

Example 3

Example 3 This example specifies a for xml all subquery as a value in an insert or update command:

create table docs_xml(id integer, doc_xml xml)
insert into docs_xml
   values(1,(select * from sysobjects for xml all)
where id=1

Examples—results

This set of examples shows the results generated by the commands in the examples above.

Example 1 This example shows a basic select for xml statement result.

select "a", 1 for xml
-----------
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <C1>a</C1>
    <C2>1</C2>
  </row>

</resultset>

(1 row affected)

Example 2 This examples shows for xml schema, returning the XML schema that describes the result set in Example 1.

select "a", 1 for xml schema
--------------
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 xmlns:sqlxml="http://www.iso-standards.org/mra/9075/sqlx">

<xsd:import namespace="http://www.w3.org/20001/XMLSchema"
 schemaLocation="http://www.iso-standards.org/mra/9075/sqlx.xsd"/>

<xsd:complexType name="RowType.resultset"
  <xsd:sequence>
   <xsd:element name="C1" type="VARCHAR_1"/>
   <xsd:element name="C2" type="INTEGER"/>
  </xsd:sequence>
</xsd:complexType>

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

<xsd:simpleType name="VARCHAR_1">
  <xsd:restriction base="xsd:string".
   <xsd:length value="1"/>
  </xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name="INTEGER">
  <xsd:restriction base="xsd:integer">
   <xsd:maxInclusive value="2147483647"/>
   <xsd:minInclusive value="–2147483648"/>
  </xsd:restriction>
</xsd:simpleType>

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

(1 row affected)

Example 3 This example of using for xml all returns the schema, DTD, and data for the result set.

select 'a', 1 for xml all
-----------
<multiple results>

<multiple-results-item type="result-set">
<multiple-results-item-dtd>


<!DOCTYPE resultset [
<!ELEMENT resultset(row*)>
<!ELEMENT row (C1,C2)>
<!ELEMENT C1(#PCDATA)>
<!ELEMENT C2(#PCDATA)>

]>


</multiple-results-item-dtd>

</multiple-results-item-schema>

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

 <xsd:import namespace="http://2=www.w3.org/2001/XMLSchema"
  schemaLocation="http://www.iso-standards.org/mra/9075/sqlx.xsd"/>

<xsd:complexType name="RowType.resultset">
 <xsd:sequence>
  <xsd:element name="C1"type="VARCHAR_1" />
  <xsd:element name="C2"type="INTEGER" />
 </xsd:sequence>
</xsd:complexType>

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

<xsd:simpleType name="VARCHAR_1">
 <xsd:restriction base="xsd:string">
  <xsd:length value="1"/>
 </xsd:restriction>
</xsd:simpleType

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

</xsd:schema>

</multiple-results-item-data>

<multiple-results-item-data>

<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <row>
  <C1>a</C1>
  <C2>1</C2>
 </row>

</resultset>

</multiple-results-item-data>

</multiple-results-item>

</multiple-results>

(1 row affected)