Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First posted :
03/24/2008
Times viewed :
347
SQLXML - FOR XML mode
SQL Server 2000 introduces support for returning data directly in an XML
format. For that purpose Microsoft added an extension to its dialect of the
Transact-SQL language. The XML XML clause, appended to a SELECT statement as
shown below, indicates to SQL Server to return the matching rows as an XML
document rather than the traditional rowset format.
SELECT FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]
The mode parameter controls the format of the returned XML document. Tables
15.1 lists the modes SQL Server 2000 understands. The optional parameters of
the FOR XML clause (listed in table 15.2) refine the format further.
NOTE: FOR XML queries return XML fragments, not well-formed documents. The
fragment does not group the results under a single root element to simplify
adding the results into already existing XML documents.
1.1 The
Modes of SQL Server FOR XML queries control the generated XML format at a high
level.
AUTO
Every row matching the query results in an element in the return XML
document. Every selected column maps to an attribute or a child element,
depending on the other parameters
EXPLICIT
The query is in a special format that describes how to map columns to XML
elements and attributes.
RAW
Every field in the query result is returned as an attribute of an element
named “row”
NESTED
Same as AUTO mode, but requires SQLXML client-side formatting.
The ExecuteXmlReader() on the SqlCommand class returns an XmlReader object
populated with the results from the command execution. Consequently,
ExecuteXmlReader() can only be used with statements returning XML formatted
data – like the statements with the FOR XML clause, as in the snippet below:
SqlCommand cmd = conn.CreateCommand();
cmd.Text = "select SupplierID, Contacs from " +
"Suppliers for XML AUTO";
XmlReader reader = cmd.ExecuteXmlReader();
DataSet dataset = new DataSet(reader);
reader.Close();
doc.Save(Console.Out);
Those of you familiar with ADO.NET will notice that this example uses the
same SqlCommand class used to query relational data from SQL Server. In this
example, we remember that the DataSet can load its data from an XML source,
like an XmlReader. Note that the DataSet is can load an XML fragment. It does
not require well-formed XML like the XmlDocument does.
Make sure to close the returned XmlReader before opening any new readers,
just like you do with any other DataReader classes. If you forget to close it,
your application will sooner or later run out of resources and quit functioning
properly. Now let’s take a closer look at the other modes of the FOR XML
clause.
1.2
Optional Parameters of the FOR XML clause. The parameters allow finer control
of the format of the generated XML.
Parameter
Description
XMLDATA
Adds and XSD schema describing the XML format of the returned resultset to
output
ELEMENTS
Specifies that the columns are to be returned as child elements rather
than attributes. This is only allowed with AUTO mode.
BINARY BASE64
Specifies that binary data is to be returned in base64-encoded format. We
must provide this option to retrieve binary data in RAW or EXPLICIT mode.
Understanding the FOR XML clause modes
Each mode of the FOR XML clause allows a different level of control over the
format of the returned XML document, ranging from no control with FOR XML RAW
to full control with FOR XML EXPLICIT. This section briefly describes each mode
together with some examples to run against the Southrain example database we always
refer to in this article. Once you downloaded the files from the book’s web
site and set up the database you can run these examples in SQL Query Analyzer
to see the results.
