ADO.NET in a Nutshell

SQL Server 2000 XML

Finally, SQL Server also provides its own direct support for XML. By using the FOR XML clause in a SELECT query, you indicate that the results should be returned as XML. This technique is a bit of a compromise. Even though it provides XML-savvy development houses with an easy way to work natively with XML, it’s also unavoidably specific to SQL Server, and therefore won’t suit if you need the flexibility to migrate to (or incorporate data from) another platform such as Oracle or DB/2.

By default, the SQL Server XML representation isn’t a full XML document. Instead, it simply returns the result of each record in a separate element, with all the fields as attributes (a marked different from ADO.NET’s default, which includes all fields as elements).

For example, the query:

SELECT CategoryID, CategoryName, Description FROM Categories FOR XML AUTO

returns the following XML document:

<categories categoryID="1" categoryname="Beverages" description="Soft 

drinks, coffees, teas, beers, and ales"/>

<categories categoryID="2" categoryname="Condiments" description="Sweet and 

savory sauces, relishes, spreads, and seasonings"/>



<!-- Other categories omitted. -->

It’s possible to reverse SQL Server’s preference by adding the ELEMENTS keyword to the end of your query. For example, the query:

SELECT CategoryID, CategoryName, Description FROM Categories 

FOR XML AUTO ELEMENTS

returns the following document:

<Categories> <CategoryID>1</CategoryID> <CategoryName>Beverages</CategoryName> ...