Bit of a beginner question on SQL-XML

Hello there, I have a very basic question but I'd appreciate some help. My query results in SQL QUERY ANALYSER with a FOR XML clause are not yielding correctly formatted results. I am expecting to see XML elements returned on individual lines. Instead my results are returned all concatenated onto one line and truncated after 256 characters (the default for a column in my results tab). I can increase the number of characters but that does not improve the formatting. Large result sets produce multiple rows but the next row does not start where the previous one finished, so the correct results cannot be reconstructed even by manually adding carriage returns

e.g.

My query

SELECT Code, ClassName FROM CLASSMAPPING
FOR XML RAW

returns the results like this, it's a single line of text word-wrapped and truncated after 256 characters:

<row Code="AUS" ClassName="Australia"/><row Code="CAN" ClassName="Canada"/><row Code="GBR" ClassName="UK"/><row Code="IRL" ClassName="Ireland"/><row Code="NZL" ClassName="New Zealand"/><row Code="PAR" ClassName="Partner schools"/><row Code="USA" ClassName=

But I surely I should really see results laid out like this:

<row Code="AUS" ClassName="Australia"/>
<row Code="CAN" ClassName="Canada"/>
<row Code="GBR" ClassName="UK"/>
<row Code="IRL" ClassName="Ireland"/>
<row Code="NZL" ClassName="New Zealand"/>
<row Code="PAR" ClassName="Partner schools"/>
<row Code="USA" ClassName="USA"/>

I can't find any information about a switch or trigger to make this work but all the literature I've read indicates it should work. Has anyone seen this before? Can you help?

I have SQL server 2000 with W2K3 server.

I don't think FOR XML does any attempt to format the XML it generates for human reading so you have to live with lack of line breaks and indentation. If you look at the result in an XML editor then it should provide you an option to pretty print the XML.

|||

I am not an expert but I am trying to learn like you; maybe something like this?

Code Snippet

declare @.xml xml
set @.xml = '<row Code="AUS" ClassName="Australia"/><row Code="CAN" ClassName="Canada"/><row Code="GBR" ClassName="UK"/><row Code="IRL" ClassName="Ireland"/><row Code="NZL" ClassName="New Zealand"/><row Code="PAR" ClassName="Partner schools"/>'

select x.t.query('.') as Results from @.xml.nodes('/row') x(t)

/*
Results
-
<row Code="AUS" ClassName="Australia" />
<row Code="CAN" ClassName="Canada" />
<row Code="GBR" ClassName="UK" />
<row Code="IRL" ClassName="Ireland" />
<row Code="NZL" ClassName="New Zealand" />
<row Code="PAR" ClassName="Partner schools" />
*/

|||Are you sure about this? Many queries on an average database will generate far more than the maximum 8192 characters for a single line. The results out of QUERY ANALYSER will be pretty much unusable in almost every case. That doesn't seem very sensible. Surely at least each <row /> element (FOR XML RAW) would drop into a separate results row?
|||I have not done much with SQL Server 2000 but with SQL Server 2005 the XML generated with FOR XML is all on one line. However SQL Server Management Studio or Management Studio Express has a built-in XML editor pane where it pretty prints and colorizes the XML for human viewing.|||OK, thanks for confirming this.
|||

I have, I think, the same problem; given N rows, return a recordset with N xml documents.

The 'FOR XML EXPLICIT' looks like the way to go, except that I can't break up the result into distinct rows.

|||

Could be that this solution will help;

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1702105&SiteID=1