How to append header to XML file generated using BCP in SQL

[miamikk]

I am generating XML files using FOR XML AUTO, ELEMENTS with BCP in SQL Query. When I try to open an the XML file in browser, I get an error that "Only one Top level element is allowed in an XML document". I am assuming the XML document is missing the header line <?xml version="1.0" ........etc etc.

How do I append this header to the XML file in the run time ?

Images of Error, XML in Notepad, and typical Table I would to create from XML.
http://www.fiu.edu/~atmakurk/kk/XML_Error.jpg
http://www.fiu.edu/~atmakurk/kk/XML_View.jpg
http://www.fiu.edu/~atmakurk/kk/Table_View1.jpg

The SQL Query I am using is below:

Declare @SQuery nvarchar(3000)

Set @SQuery = 'bcp "EXEC test..free_Cust omsDistrict_HS4 _TEST [2006exp], 21, 6, 2" QueryOut "' + @fName + '" -c -t, -T'

Exec master..xp_cmds hell @SQuery



I am a new to XML and also newbie programmer. What I am really trying to do with the XML files is to display them as tables in webpages.
I would appreciate if anyone can direct me how to proceed ? How do I retrevie the XML file from Front End (ASP.NET) ?

[jkmyoung]

What the error refers to is not having a root element. Eg, the following would be invalid as an xml document:
[CODE=xml]<row>
<value>1</value>
</row>
<row>
<value>2</value>
</row>[/CODE]
You'd need to wrap these elements in a root element like so:
[CODE=xml]<table><row>
<value>1</value>
</row>
<row>
<value>2</value>
</row>
</table>[/CODE]

You have multiple d elements at the root level.

[miamikk]

I fixed the problem by adding ROOT option in the FOR XML part.