I just modified the SQL you sent and put the XML data in # table and wow it is much faster.

INSERT INTO #xmlData

SELECT CAST((SELECT '' + line

...

I don't see much difference in performance. I have heard that OPENXML is faster, never used though. Do you think using OPENXML will be much faster? If so, how will...

Nevermind.... I figured it out. Added a ID column and use the ID in the while.

SELECT @RecordID = MIN(ID) from #tblGroups

WHILE @RecordID IS NOT NULL

BEGIN

;WITH XMLNAMESPACES ( default 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-12-28T21:32:27')

SELECT

c.value('field1[1]','varchar(100)'),

c.value('field2[1]','varchar(100)')

--, other nodes here

FROM

YourTableName y

CROSS APPLY yourXmlCol.nodes('//myFields') t(c);

Nevermind... I got the SQL query.

Thanks.....

Yes, the table has 4 columns but the data for 5 directores and it's permissions is stored in single record. I want the output to have 5 rows instead of...

Thanks for your help !

The query works great... The only column which I am not able to shred is the <DataSet Name="">. Any help on this is appreciated.

-<DataSets>

+<DataSet Name="GetLastCalendarDate">

+<DataSet Name="PrevDate">

+<DataSet...

Tried with the new query but performance is a issue. If anybody can help with OPENXML query that would be great. I know in the past some has said that...

Dave, can you be more specific on which underlying design is wrong and which sort of problem I will be fighting?

Attached are the 2 execution plans (with and without variable). Regarding disbaling parameter sniffing, after we upgraded our databases to SQL 2008 R2 from SQL 2005 version many of our...

How about Reporting services on SQL Server 2005 32 bit be able to pull data from SQL Server 2008 64 bit databases?

So only Enabling /PAE should also help.., right?