Mental Jetsam

By Peter Finch

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);
            /* ... */
        }
    }
}
Advertisements

3 Responses to “C# SQL Server result in XML”

  1. Usha said

    how to show dom data on web page

  2. Barklay said

    Use “return doc.OuterXml;” in your function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: