FOR XML clause and <root> element

Hi,
When I run the following query in Query Analyzer, it works fine, but I
cannot add <ROOT> ... </ROOT> element to result:
SELECT * FROM
(
select top 10
CompanyName,
SUM((CONVERT(money,("Order
Details".UnitPrice*Quantity*(1-Discount)/100))*100)) AS Amount
, count(*) OrderItemCount
from
customers
inner join orders on orders.CustomerID = customers.CustomerID
inner join [Order Details] on Orders.OrderID = [Order Details].OrderID
GROUP BY
CompanyName
) AS GoodCustomers
FOR XML AUTO ,ELEMENTS
Ho can add <root> element to the result?
Thank you,
AlanHi
You can do that externally such as http://sqlxml.org/faqs.aspx?faq=10
This may also help http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp
John
"A.M" wrote:

>
> Hi,
>
> When I run the following query in Query Analyzer, it works fine, but I
> cannot add <ROOT> ... </ROOT> element to result:
>
>
> SELECT * FROM
> (
> select top 10
> CompanyName,
> SUM((CONVERT(money,("Order
> Details".UnitPrice*Quantity*(1-Discount)/100))*100)) AS Amount
> , count(*) OrderItemCount
> from
> customers
> inner join orders on orders.CustomerID = customers.CustomerID
> inner join [Order Details] on Orders.OrderID = [Order Details].OrderID
> GROUP BY
> CompanyName
> ) AS GoodCustomers
> FOR XML AUTO ,ELEMENTS
>
>
>
>
>
> Ho can add <root> element to the result?
>
> Thank you,
> Alan
>
>|||Hi,
Additional to what John recommanded, there is a new property that you can
use to tell the OLEDB provider to add
the root tag for you.
command.Properties("Output Stream") = response
command.Properties("XML Root") = "root"
command.Execute , , 1024
Then the stream you are getting back will have "XML Root" as the document
root tag.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.