Re: changing the results header with for xml

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

--
Managing knowledge is just as important as managing technology.


"Martin Honnen" wrote:

Tristan wrote:

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?

Try 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 Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

.

Relevant Pages

  • Re: DISCOVER_XML_METADATA
    ... Small XML is not problem. ... and then run following for each database: ... unprocessed cubes and it doesn't show processing status information ... A DBSCHEMA_CATALOGS query will get you a list of all the databases ...
    (microsoft.public.sqlserver.olap)
  • RE: Query producing XML appears to be cached
    ... By default the XMLDataSource control always caches its data, ... I have an app that executed a sql server query that produces an XML ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Typed XML slows down query?!
    ... > Hi Martin, ... >>I have a performance problem with typed XML and a simple query. ... >> CREATE PRIMARY XML INDEX idx_article ...
    (microsoft.public.sqlserver.xml)
  • setting dataset datarelation from database
    ... I want to set my dataset's datarelations based on the relationships ... I had a query I ... switched to using 'for xml explicit, ... If I run the query in sql query analyzer, I see the schema ...
    (microsoft.public.dotnet.framework.adonet)
  • Query producing XML appears to be cached
    ... I have an app that executed a sql server query that produces an XML ... actually executing. ...
    (microsoft.public.dotnet.framework.aspnet)