When you extract elements you should use the element name (e.g. columnid[1]).

The way you wrote the code you're trying to query attributes.

Here are both versions for comparison and to show...

Instead of

WHERE Date BETWEEN '1/1/2009' and '12/31/2009 23:59:59.997'

I'd rather use

WHERE Date >= '20090101' and Date<'20100101'

The main reason for date formatting YMD instead of M/D/Y is to have no...

The max size is for data stored with xml data type is 2GB. I don't know if your file would exceed those limit...

Regarding extracting node names and values I would...

Peter Brinkhaus (2/23/2010)

---

Excellent job, Peter!!

If I had...

First and second cte are used to populate the count() values as requested.

The CROSS JOIN will result in a cartesian product of all rows from the first cte (cte) and...

Please have a look at BOL (BooksOnLine, the help system installed with SQL Server). Especially look for "Manufacturing Scenarios, example A: Viewing a multilevel bill-of-materials list for a parent product"

It...

My last post didn't recognize your latest reply. So please ignore it.

Glad it finally worked out. 🙂

Would you please provide the script you're using to update your table together with some sample data that will show the effect you get?

What exactly do you mean by "getting truncated"? Since you only do a SELECT it might be the influence of the length setting for the output of SSMS (Management Studio)....

Here...

What is the specific reason for building one table per month instead of adding the related column to one table and (if required) use horizontal partitioning?

How many rows you end...

I'm still not 100% sure what value your percentage calculation will be based on. But maybe the following code will get you started.

Please note how I provided sample data in...

Drammy (2/23/2010)

---

Ah, thanks guys - I have actually already used a tally table in another part of the same solution, although it was a couple of years ago, clever stuff...

I'll...

It's interesting to see that SET DATEFIRST actually takes precedence over SET LANGUAGE. I couldn't find any hint about it in BOL so far...

Here's an example:

If you change the language...

Following please find an example of how I would do it.

Instead of creating the numbers table on the fly I usually would use my Tally table. If you don't have...

Like I stated before:

change the communication between your app and the db to stored procedures only and lock down everything else. Once you're done with it, add your users to...