C# SQL Server result in XML
Posted by pcfinch on April 5, 2009
Formatting the output of a SQL query in XML using SQL Server is really simple and
very powerful if you want to deal with XML in the applications instead of normal
Record sets. The following is a simple SQL Query to
produce the results in XML. The XML essentially comes back to the application
as if it were one column of a result set with one row.
SELECT username AS '@username', password AS 'password'
FROM [dbo].users
WHERE username = @USERNAME
FOR XML PATH('User'), ROOT('Users'), TYPE ;
The PATH(‘User’) option creates an XML ‘User’ element for each row in the
result set and the ROOT(‘Users’) option wraps all the results in the ‘Users’ element.
For each of the columns in the result set simple specify the element name you want
the data to appear in (e.g. ‘password’) or put a ‘@’ at the start of the name
to place the contents of the column in an attribute of the row element
(e.g. ‘@username’). The output of the query is as follows.
<Users>
<User username="fmcske">
<password>password</password>
</User>
</Users>
If your using C#, the following example code can be used to read the
results of the query and create a DOM object.
using (SqlConnection connection = new SqlConnection(sConnectString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
DateTime startTime = DateTime.Now;
command.CommandText = sSqlQuery ;
command.Parameters.Add("@USERNAME", SqlDbType.VarChar, 32) ;
command.Prepare();
command.Parameters["@USERNAME"].Value = sUsername ;
using (XmlReader reader = command.ExecuteXmlReader())
{
XmlDocument dom = new XmlDocument();
dom.Load(reader);
/* ... */
}
}
}
