Here's my interpretation of the numbers:

Query 1:

There really seems to be no big difference. Run it with a larger data set to see which query scales better. You could also...

Do you know the structure of your XML file?

If so, can you post some sample data to describe the structure?

RBarryYoung (2/28/2010)

---

...

What I did notice though is that adding the text function ("(.../text())[1]") really seems to help the optimizer to produce a much more efficient XML query (I actually learned...

Paul White (2/28/2010)

---

You're right in terms of execution time. But when...

I compared...

A similar approach to Pauls VIEW concept, but a different way to query the results:

You could insert the result set of your CTE (which would become a normal SELECT statement)...

tabrez.test (2/27/2010)

---

also ihave tried above sql they are also displaying policies which have trans add and trans can and in between other trans like ren,adj,,,(which should not )

i want...

Great you look at it that way!!

And nothing...

PhilM99 (2/27/2010)

---

I was preparing some data to demonstrate the conversion error when I discovered the problem, which was not with the MAX statement, but with the CASE statement in the...

I haven't seen a scenario where the MAX() function on character values did not work.

Would you mind posting some of the data that will fail in your scenario?

Based on the...

Nothing to be sorry for!

If it's any different than mine you should keep it posted. Two...

If number of columns and data type would be identical for table 1 and table2 I guess Pauls approach would be the way to go.

But that doesn't seem to be...

I would use a self join on a numbered CTE.

I don't know how you determine the row that's "immediate next" so I assumed a id column. The reason why I...

It's kind of a PIVOT but this scenario is also sometimes referred to as CrossTab.

To get the individual numbers grouped together it's better to use the PARTITON BY clause of...

lmu92 (2/27/2010)

---

There seems to be something wrong in your sample data:

table dbo.TempUserTags has three entries for TagId=1 (C Sharp). (ID 1,7, and 17)

Why do you expect C Sharp existence in...