FOR XML AUTO and sql server 2005

SELECT  * FROM emp FOR XML AUTO , ROOT ('Employees') , ELEMENTS 

this query return value in xml format but total xml is showing in one column and column name is showing dynamically. if i want that i will specify the column name like data etc. so xml value will show as single row & column but header or output column name will be "DATA"

is it poss开发者_JAVA百科ible if yes the please show me the way.thanks

You need to check out the new FOR XML PATH feature in SQL Server 2005 and up - read all about it in SQL Server Books Online.

Basically, with FOR XML PATH, you can define the shape of your XML very easily, e.g.

SELECT
    EmployeeID AS '@EmployeeID',
    FirstName,
    LastName
FROM dbo.Employees
FOR XML PATH('Employee'), ROOT('AllEmployees')

will generate XML something like:

<AllEmployees>
   <Employee EmployeeID="12345">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
   </Employee>
</AllEmployees>

FOR XML PATH is very flexible and allows you to do a lot of things quite easily and intuitively.