Unable to view resultset created with FOR XML

[TheBugSlayer]

Good morning all. I have a SELECT statement that includes a FOR XML clause. I copy the result from SSMS and save it in an XML file. However, I am not able to view the file in IE...it returns

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


--------------------------------------------------------------------------------

Only one top level element is allowed in an XML document. Error processing resource 'file:///C:/Projects/...

<Pulse><AccountNumber>XYZ</AccountNumber><Client_Sub_Account_Number__c>ABC</Client_...

In any other XML editor the text is not formatted; it shows as one big block. Looking at the file, it' s missing the usual XML file header (ref to namespaces, etc).

How do I get the resultset to show in the proper format?

Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

If you are using SQL2005 or newer, you should try using the Root keyword. Something like this:

[tt]
Select Columns...
From Tables....
For XML auto[!], root('root')[/!]
[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Thanks George. That did not make a difference. I noticed however that when I click on the result set in SSMS (it appears as a hyperlink), it opens perfectly formatted in SSMS. Looks like I have to insert the header part. Just not sure what it consists of. Trial and error I guess.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)