shashi shashi - 4 months ago 34
SQL Question

How to save results as xml in SQL Server Management Studio?

I am trying out TDD and creating fake objects, I want to use XML from the test DB. Thus I want to create XML of the results of a query, which I am running in SQL Server Management Studio.

But I am unable to find how to get the results as XML in SQL Server Management Studio. Is this possible? And How?

Answer

You can use "FOR XML" to output the results of a query to XML.

For example:

SELECT
     o.Order_Number AS 'OrderNumber', --Element
     o.Order_Total AS '@OrderTotal' --Attribute

FROM dbo.ORDER o
FOR XML PATH('ORDER'), ROOT('ORDERS') --Path / Root let you formulate the xml the way you want
Comments