Hi,

I'm getting results out of sql server 2005 as xml in the desired format
except the results header which is put before the xml. Any ideas how to
remove or modify the results header when returning data for xml? For example,
with the following query...

select top 1 *
from OrderHeader
WHERE limitprice is null
for xml auto, elements xsinil, ROOT('myorders')

....the xml returned (below) is headed with this row:
"XML_F52E2B61-18A1-11d1-B105-00805F49916B".

I can change column headings normally with sql results but how can it be
done for the single result column of a for xml query? My xml is below. I
don't know what to call this row other than a header so can't find a prevoius
solution to this problem so apologies if I am reproducing a many-times-asked
question.

Thanks,

Tristan


p.s. an example result
XML_F52E2B61-18A1-11d1-B105-00805F49916B
<myorders xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OrderHeader>
<OrderHeaderID>37424</OrderHeaderID>
<OrderRef>37424</OrderRef>
<ProductTypeID>10</ProductTypeID>
<LimitPrice xsi:nil="true"/>...

Tristan wrote:

Show quoteHide quoteTry like this:

SELECT (SELECT TOP 1 *
FROM Orders
FOR XML AUTO, ROOT('ORDERS'), TYPE) AS XmlCol;

so for your query that should be

SELECT (select top 1 *
from OrderHeader
WHERE limitprice is null
for xml auto, elements xsinil, ROOT('myorders'), TYPE) AS XmlCol;

where 'XmlCol' is the column name you want to have.

Martin's solution (thanks Martin!) can be adapted to give the XML declaration
as the first line in xml produced by a 'for xml' query:
e.g.
select (
select top 1 *
from tablename
for xml auto, elements xsinil, ROOT('myXml'))
as '<?xml version="1.0" encoding="utf-8"?>'

....gives:

<?xml version="1.0" encoding="utf-8"?>
<myXml xmlns:xsi="http://www.w3.org/2001/XMLSchema-Instance">
<OrderHeader>
<OrderHeaderID>37424</OrderHeaderID>
....

Thanks for the quick reply Martin.
Tristan

Show quoteHide quote

XML EXPLICIT Help
Obtain Data from XML column
Bulk load and XSD problem...
Obtaining data from an XML column to a document
Import XML with multiple sublevel nodes
XML parsing: ... illegal xml character
missing elements while passing XML from .NET to SQL 2005
Need help w/ importing data via web services
Retrieving XML Field data as Text
dealing with special characters using SP_XML_PREPAREDOCUMENT