Click here to Skip to main content
Click here to Skip to main content

Serving SQL XML through a WebService (SQL 2005)

By , 3 Feb 2007
Rate this:
Please Sign up or sign in to vote.

Introduction

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
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.

History

  • 4th February, 2007: Initial post

License

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 http://codeconsults.com/. See his blog here
Personal homepage http://russquinn.com/

Comments and Discussions

 
QuestionHello I am using this code (the one u mentioned) Pinmembertashwey12-Jan-12 6:37 
QuestionWCF Sample? PinmemberRruedi7-Sep-07 4:47 
AnswerRe: WCF Sample? PinmemberRuss Quinn7-Sep-07 4:51 
GeneralNested XML PinmemberTancev Sasa6-Feb-07 19:00 
GeneralRe: Nested XML PinmemberRuss Quinn7-Feb-07 6:33 
GeneralRe: Nested XML PinmemberTancev Sasa7-Feb-07 7:44 

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

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

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