Why does Query Analyzer always truncate the output from a FOR XML query, regardless of whether you send the results to a file or the screen?
When SQL Server 2000 executes a FOR XML query, SQL Server returns a resultset that contains one text column and one or more rows. The column has a unique and unusual name: XML_F52E2B61-18A1-11d1-B105-00805F49916B (which I refer to here as the XML column). This special name lets the SQL Server OLE DB provider recognize the column as a stream of XML data; you can see this behavior easily in Query Analyzer by running a FOR XML query. SQL Server divides the XML result in the XML column into rows, where each row can contain up to 2033 characters. Constructing the XML stream from this XML resultset for each query would be very inconvenient. Thankfully, Microsoft extended both ADO and the SQL Server OLE DB provider to provide a stream of XML. The ADO and SQL Server OLE DB extensions let you return an XML query's results in an IStream or ADO Stream object instead of a standard ADO Recordset object or OLE DB rowset. ADO and OLE DB construct the stream by joining the rows of the XML column. (For details about the ADO and OLE DB XML extensions, see SQL Server Books Online—BOL.)
Now, let's look at how SQL Server's use of the XML column to return an XML result affects Query Analyzer's behavior. Surprisingly, Query Analyzer doesn't use the ADO or OLE DB extensions when executing a FOR XML query; Query Analyzer uses ODBC. And because Microsoft hasn't updated the ODBC driver to be XML-aware, the ODBC driver treats the data like any other text column. If you retrieve the results to a file or screen, the concatenation of multiple rows in the resultset causes extra line breaks. In addition to extra line breaks, data truncation can occur.
Truncation can occur when Query Analyzer's Maximum Characters per Column setting contains a value smaller than 2033. To fix the truncation problem, you can change this setting from its default value of 256 characters in Query Analyzer, Tools, Options, Results. This change solves the data-truncation problem, but line breaks still occur in the concatenation of data from two rows. The only way to avoid generating the line breaks is to use ADO, OLE DB, or an HTTP query and the SQL Server Internet Server API (ISAPI) DLL to obtain an XML result from SQL Server.
Another useful but undocumented feature can help you read the XML result that Query Analyzer displays. SQL Server supports using trace tags to alter the behavior of certain functions. If you set trace tag 257 on, SQL Server invokes a "pretty-print" algorithm on the XML output before returning it to make the XML result more readable. You can turn on the trace tag with the following statement:
DBCC traceon(257)
I frequently use the trace tag when coding examples for this column. I think you'll find the trace tag useful for obtaining more readable XML results when you use Query Analyzer for experimenting and prototyping.
Prev. page
[1]
2
next page 
