Serving SQL XML through a WebService (SQL2005)

Introduction

I recently needed to create a feed for a customer from some data held within SQL Server so looked around for the best (and quickest) way to do this. Knowing that I use SQL 2005 and that XML was an intregal part of it, I looked at ways to leverage this.

After some thought I came up with the idea of serving XML from the database and creating a WebService that the customer can call whenever they need some data (with some parameters if required).

Now, I know you're all saying "This is nothing new or difficult" and that's exactly the point of writing this article. It is easy and as such may be easily overlooked. I couldn't believe how simple it was and there didn't seem to be any other articles on CodeProject telling anyone how to solve this.

Using the code

Firstly, using your SQL 2005 database (with some data already in it), create a stored procedure like so

CREATE PROCEDURE [dbo].[GetStories]
    @fromDate datetime,
    @toDate datetime
AS 
    BEGIN
 
        select  dbo.Story.id,
                description,
                notes,
                text,
                publicationdate,
                authorsnames,
                keywords
        from    dbo.Story
                inner join dbo.Status on dbo.Story.StatusId = dbo.Status.id
        where   publicationdate between @fromDate and @toDate
                and dbo.Status.status = 'live'
        order by publicationDate
        FOR     XML PATH('story'),
                    ROOT('stories')
    END

The key to this step is the FOR XML PATH(###), ROOT(###) part. This tells SQL Server to return XML with each row having the element name of story and the root of the XML document to be stories

Next, create a normal WebService using the usual project options and add a new WebMethod. In this method, connect to the database and get the data.

Now, as I want XML back I call the ExecuteXmlReader method on the SqlCommand class like so:

  XmlReader reader = command.Command.ExecuteXmlReader();

This reader can then be streamed into an XmlDataDocument which can then be returned from the WebMethod. Below is my copy of the WebMethod, where you'll notice a simple Database class and App_Data.SqlSPCommand to simplify the calls to the database

[WebMethod(Description = "Get stories based on a centre, and a from and to date",
CacheDuration = 600, MessageName = "GetStoriesForCentre")]
public XmlDataDocument GetStoriesForCentre(string centre, DateTime fromDate, DateTime toDate)
{
 Database db = new Database("TarkStoriesConnectionString");
 
 using (db.Connection)
 {
  db.OpenConnection();
 
  App_Data.SqlSPCommand command = new App_Data.SqlSPCommand("GetStoriesForCentre", db.Connection);
  command.AddParameter("@centre", SqlDbType.VarChar, centre);
  command.AddParameter("@fromDate", SqlDbType.DateTime, fromDate);
  command.AddParameter("@toDate", SqlDbType.DateTime, toDate);

  XmlReader reader = command.Command.ExecuteXmlReader();
 
  XmlDataDocument xml = new XmlDataDocument();
  xml.Load(reader);

  return xml;
 }
}

And that's it.....

Call the WebService in the normal way, supply some query string parameters (in my example 2 dates) and you should get some XML back like so:

<?xml version="1.0" encoding="utf-8" ?>
<stories>
    <story>
        <id>514</id>
        <description>some description</description> 
        <notes>no notes available</notes> 
        <text>blah blah blah</text> 
        <publicationdate>2007-01-30T00:00:00</publicationdate> 
        <authorsnames>Sue Williams</authorsnames>
        <keywords>boring story</keywords>
    </story>
</stories>

You may want to tidy up the loading of the XmlReader into the XmlDataDocument class by first checking to see if there is any content (use MoveToContent and check the result), but for now i'll leave that up to you all..

Points of Interest

This is just so simple that hopefully I can help someone by pointing out the obvious that may have been overlooked.