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

Question

I am trying to use TDD and create fake objects, I want to use XML from a test database. Thus, I want to create XML of the query results that I run in SQL Server Management Studio.

But I can not find how to get the results as XML in SQL Server Management Studio. Is it possible? And How?

Answer 1

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

For instance:

 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