Retrieving large xml from columns of type 'xml' in sql server 2005, using WCF

Hi Friends,

One of my projects need help in solving the following issue.

Our database contains a table with columns of xml type. These xml columns contain large xml documents.

For example: A Single row in the table is as below.

Above 4 columns contains large xml documents.

This table is filled by another stored procedure, which collects data from multiple tables and forms xml documents (using 'for xml' in select query) and inserts into the above table. So the size of the xml documents is not fixed.

Table definition is as below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Requests](

                [ID] [bigint] NOT NULL,

                [GUID] [uniqueidentifier] NOT NULL,

                [License Info]   xml   NULL,

                [Profile Info]   xml    NULL,

                [Transaction Info]   xml    NULL,

                [Product Info]    xml  NULL,

 CONSTRAINT [PK_Requests] PRIMARY KEY CLUSTERED

(

                [ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

'ID' has primary key as well as clustered index

'GuId' has non clustered index.

Database:            SQL Server 2005

Front-End:          .Net  ( WCF)  2005/ 2008

Language:           C#

Requirement:

Our WCF has to fetch the above 4 xml documents from the database table. And we have to return this to client application. We are not displaying that data anywhere; our job Is just pull the data from the database and pass it to the client applications (Web/ Windows).

We will develop the client app later. Based upon the output from the WCF (DataSet/ Xml/ Object) the methods in the client application will be developed.

Problem:

 When the xml columns contain a huge xml document, even simple select query is also taking approximately 25 minutes to retrieve the data in SQL Server Management Studio.

Always we will fetch the data by passing the Request Id only.

 Ex: Select  * from Request (NoLock) where Id = 1

Need help in:

How to retrieve the data from the above table as fast as possible, irrespective of the size of the xml document in those columns?

After retrieving the data from the database, what is the best way/ format that our WCF will use to pass it back to the client application?

Please respond with your suggestions as early as possible.

Thanks,

Madhu