FOR XML clause and <root> element

12 Aug 2005 3:06 AM

A.M

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

12 Aug 2005 7:24 AM

John Bell

Hi

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

Show quote

"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
>
>
>

12 Aug 2005 8:07 AM

Michael Cheng [MSFT]

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.