You need to add the namespace declaration, especially the DEFAULT declaration:

;WITH XMLNAMESPACES

('http://www.w3.org/2001/XMLSchema-instance' as xsi, -- not required for this specific query

'http://schemas.xmlsoap.org/soap/encoding/' as soapenc, -- not required for this...

What have you tried so far?

Please post your current solution.

I think we need some sample data. You could also reduce the table def to the columns required. This would also reduce the amount of sample data to be generated...

Wouldn't it be easier to do it in one query? Or am I missing the obvious?

SELECT land1, COUNT(*)

FROM kna1

WHERE land1 IN('SE', 'FI')

GROUP BY land1

Well, maybe the information provided so far cannot be used to answer your question...

Please attach both xml files and provide the DDL script for the [File] table.

Create a user role with SELECT permissions on the views and DENY SELECT on the source tables. This will be a lot easier to setup and maintain than the trigger...

Please post table def and some sample data in a ready to use format for the following tables:

CASES, PROPERTY, CASEEVENT, and CR_REPORTPERIODS.

Also, please post your expected result based on those...

You could wrap your UNION statment in a subquery or CTE. This way you could save one sorting operation by moving the ROW_NUMBER() function to the outer SELECT.

At a first...

First thing to say: storing date values as varchar is a really bad design idea. SQL Server came up with DATETIME and SMALLDATETIME for as reason!

Regarding the error: Other than...

Please post table def and some sample data in a ready to use format. There might be an easier way to do it...

ColdCoffee (8/2/2010)

jvanderberg (8/2/2010)

This will convert a date into the first Monday of the week that contains it. Group on this date.

Check out Lynn Pettis' wonderful...

Yes: see my previous post.

I was hoping you just selected the wrong forum to post in so we could use ROW_NUMBER()...

But since you're at SQL2000 I would use the "quirky update method" as described...

Please confirm that you're using SQL2000 and not 2005 or 2008.

If SQL2000: can we add one more column to the temp table as well as a clustered index?

Here's what I came up with. Since I didn't know if you need the values for attribute B or the element name A I included both.

I don't know of a...