FOR XML Enhancements


The FOR XML clause was first introduced to the T-SQL SELECT statement in SQL Server 2000. It has been enhanced in SQL Server 2005. Some of the new capabilities that are found in the FOR XML support in SQL Server 2005 include support for the XML data type, user-defined data types, the timestamp data type, and enhanced support for string data. In addition, the FOR XML enhancements also include support for a new Type directive, nested FOR XML queries, and inline XSD schema generation.

Type Directive

When XML data types are returned using the FOR XML clauses’ Type directive, they are not serialized. Instead the results are returned as an XML data type. You can see an example of using the FOR XML clause with the XML Type directive here:

SELECT DocID, MyXMLDoc FROM MyXMLDocs   WHERE DocID=1 FOR XML AUTO, TYPE

This query returns the relational DocID column along with the MyXMLDoc XML data type column. It uses the FOR XML AUTO clause to return the results as XML. The TYPE directive specifies that the results will be returned as an XML data type. You can see the results of using the Type directive here:

<MyXMLDocs Doc>   <MyXMLDoc>     <MyXMLDoc xmlns="MyXMLDocSchema">       <DocumentID>1</DocumentID>       <DocumentBody>My New Body</DocumentBody>     </MyXMLDoc>   </MyXMLDoc> </MyXMLDocs>
Note 

The Type directive returns the XML data type as a continuous stream. I added the formatting to the previous listing to make it more readable.

Nested FOR XML Queries

SQL Server 2000 was limited to using the FOR XML clause in the top level of a query. Subqueries couldn’t make use of the FOR XML clause. SQL Server 2005 adds the ability to use nested FOR XML queries. Nested queries are useful for returning multiple items where there is a parent-child relationship. One example of this type of relationship might be order header and order details records; another might be product categories and subcategories. You can see an example of using a nested FOR XML clause in the following listing:

SELECT DocID, MyXMLDoc,   (SELECT MyXMLDoc     FROM  MyXMLDocs2     WHERE MyXMLDocs2.DocID = MyXMLDocs.DocID     FOR XML AUTO, TYPE) FROM MyXMLDocs Where DocID = 2 FOR XML AUTO, TYPE

In this example the outer query on table MyXMLDocs is combined with a subquery on the table MyXMLDocs2 (for this example, a simple duplicate of the MyXMLDocs table). The important thing to notice in this listing is SQL Server 2005’s ability to use the FOR XML clause in the subquery. In this case the subquery is using the Type directive to return the results as a native XML data type. If the Type directive were not used, then the results would be returned as an nvarchar data type and the XML data would be entitized. You can see the results of the nested FOR XML query shown in the listing that follows:

<MyXMLDocs Doc>   <MyXMLDoc>     <MyXMLDoc xmlns="MyXMLDocSchema">       <DocumentID>1</DocumentID>       <DocumentBody>&quot;My text&quot;</DocumentBody>     </MyXMLDoc>   </MyXMLDoc> </MyXMLDocs>
Note 

I added the formatting to the previous listing to make it more readable.

Inline XSD Schema Generation

Another new feature in SQL Server 2005’s FOR XML support is the ability to generate an XSD schema by adding the XMLSCHEMA directive to the FOR XML clause. You can see an example of using the new XMLSCHEMA directive in the following listing:

SELECT MyXMLDoc FROM MyXMLDocs WHERE DocID=1 FOR XML AUTO, XMLSCHEMA

In this case, because the XMLSCHEMA directive has been added to the FOR XML clause the query will generate and return the schema that defines the specific XML column along with the XML result from the selected column. The XMLSCHEMA directive works only with the FOR XML AUTO and FOR XML RAW modes. It cannot be used with the FOR XML EXPLICIT mode. If the XMLSCHEMA directive is used with a nested query, it can be used only at the top level of the query. The XSD schema that’s generated from this query is shown in the following listing:

  <xsd:import namespace="http://MyXMLDocSchema" />   <xsd:element name="MyXMLDocs">     <xsd:complexType>       <xsd:sequence>         <xsd:element name="MyXMLDoc" minOccurs="0">           <xsd:complexType sqltypes:xmlSchemaCollection="[tecadb].[dbo].[MyXMLDocSchema]">             <xsd:complexContent>               <xsd:restriction base="sqltypes:xml">                 <xsd:sequence>                   <xsd:any processContents="strict" namespace="http://MyXMLDocSchema" />                 </xsd:sequence>               </xsd:restriction>             </xsd:complexContent>           </xsd:complexType>         </xsd:element>       </xsd:sequence>     </xsd:complexType>   </xsd:element> </xsd:schema> <MyXMLDocs xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">   <MyXMLDoc>     <MyXMLDoc xmlns="http://MyXMLDocSchema">       <DocumentID>1</DocumentID>       <DocumentBody>My New Body</DocumentBody>     </MyXMLDoc>   </MyXMLDoc> </MyXMLDocs>

The XMLSCHEMA directive can return multiple schemas, but it always returns at least two: one schema is returned for the SqlTypes namespace, and a second schema is returned that describes the results of the FOR XML query results. In the preceding listing you can see the schema description of the XML data type column beginning at: <xsd:element name=“MyXMLDocs”>. Next, the XML results can be seen at the line starting with <MyXMLDocs xmlns=“urn:schemas-microsoft-com:sql:SqlRowSet1”>.

Note 

You can also generate an XDR (XML Data Reduced) schema by using the XMLDATA directive in combination with the FOR XML clause.




Microsoft SQL Server 2005 New Features
Microsoft SQL Server 2005 New Features
ISBN: 0072227761
EAN: 2147483647
Year: 2005
Pages: 80
Authors: Michael Otey

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net