Click here to Skip to main content
13,255,340 members (46,854 online)
Click here to Skip to main content
Add your own
alternative version


60 bookmarked
Posted 4 Feb 2007

Serving SQL XML through a WebService (SQL 2005)

, 4 Feb 2007
Rate this:
Please Sign up or sign in to vote.
How to serve XML from SQL 2005 through a WebService


I recently needed to create a feed for a customer from some data held within SQL Server, so I looked around for the best (and quickest) way to do this. Knowing that I use SQL 2005 and that XML was an integral 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
        from    dbo.Story
                inner join dbo.Status on dbo.Story.StatusId =
        where   publicationdate between @fromDate and @toDate
                and dbo.Status.status = 'live'
        order by publicationDate
        FOR     XML PATH('story'),

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)
  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();

  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" ?>
        <description>some description</description> 
        <notes>no notes available</notes> 
        <text>blah blah blah</text> 
        <authorsnames>Sue Williams</authorsnames>
        <keywords>boring story</keywords>

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.


  • 4th February, 2007: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Russ Quinn
Web Developer
Australia Australia
Web developer with 15 years of commercial and industrial experience in the software world.
Now working as a contractor through his own company See his blog here
Personal homepage

You may also be interested in...

Comments and Discussions

QuestionHello I am using this code (the one u mentioned) Pin
tashwey12-Jan-12 7:37
membertashwey12-Jan-12 7:37 
QuestionWCF Sample? Pin
Rruedi7-Sep-07 5:47
memberRruedi7-Sep-07 5:47 
AnswerRe: WCF Sample? Pin
Russ Quinn7-Sep-07 5:51
memberRuss Quinn7-Sep-07 5:51 
GeneralNested XML Pin
Tancev Sasa6-Feb-07 20:00
memberTancev Sasa6-Feb-07 20:00 
GeneralRe: Nested XML Pin
Russ Quinn7-Feb-07 7:33
memberRuss Quinn7-Feb-07 7:33 
GeneralRe: Nested XML Pin
Tancev Sasa7-Feb-07 8:44
memberTancev Sasa7-Feb-07 8:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171114.1 | Last Updated 4 Feb 2007
Article Copyright 2007 by Russ Quinn
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid