Retrieving Data from SQL Server Database in XML Format

SELECT * FROM FOR XML AUTO/RAW/EXPLICIT/NESTED

Apart from these there are optional parameters for the SQL query. The optional parameters as the name indicates can either be used or neglected. The optional parameters that can be used are Binary Base64, Elements, and XMLData. With the optional parameters in place the syntax would take the form,

SELECT ... FOR XML mode [, BINARY BASE64] [, ELEMENTS] [, XMLDATA]

The Binary Base64 option is used if you want to retrieve data in the binary format from the database. Binary data that is found in the database should be retrieved using this option. The modes that are used to retrieve data in binary format are the ‘raw’ and the ‘explicit’ modes.

The Elements option is used to return the data in the table as child elements. The fields of the row become the attributes of the element returned if you are not using the Elements option. Thus for each row you get an element with child elements being the fields of the row.

Auto mode is the only mode in which you can use the Elements option. If you want to define the format that is returned you need an XSD schema for that. The XMLData option allows this. This option adds a schema so that you get the format that you want for your XML data.

example:

select * from employee for xml auto

select * from employee for xml auto,elements

Fore more info:http://www.xml-training-guide.com/retrieve-sql-server-data-in-xml-format.html