It depends on the number repetitions of each element you expect in your document. Looking at this example document it looks like there is always going to be exactly 1...

Combine the both solutions. Take Paul's DENSE_RANK() to generate a unique name per 'group' and add another column, using my ROW_NUMBER() suggestion to give each individual member of the group...

Ok, then don't delete but just select the rows that have DupNR > 1. Or do I not understand your requirements correctly?

Similar to Paul's suggestion:

SELECT

ItemNum,

ROW_NUMBER() OVER(PARTITION BY Attribute1,Attribute2,Attribute3 ORDER BY (SELECT NULL)) as DupNR

FROM T1;

All rows having a value higher than 1 in DupNR...

The basic problem is that your document has a somewhat weird structure: only the root element is in namespace 'http://schemas.microsoft.com/BizTalk/EDI/X12/2006'. All contained

elements are in an unnamed namespace. By default...

If that is what you've concluded from the previous, you've not been paying attention.

You've just been explained how merge + output is faster than your piece of code and how...

:). Yes, that's how this question got to be: I did it wrong myself. And so far almost half of the people who tried, had it wrong too. To their...

Yes, sorry for the formatting of the answers. This is my 1st QoD and I couldn't find how to format the answers. So I put them in as best as...

Furthermore, do some really serious testing if you intend to put a transaction in the calling procedure surrounding the call(s) to the subprocedure(s). Errors raised in the sub procedure(s), but...

adrian.buzgau (12/13/2011)

---

Thank you for the code, but you are assuming that you know the value of claim number and that claim number value is always 1,2,3 etc

this is not true...

Just for fun, here's a query that finds all path components from your strings using a tally table:

select top 11000 identity(int, 1, 1) n

into #Tally

from master.sys.syscolumns sc1

cross...

http://www.sqlservercentral.com/Forums/Topic1168980-392-1.aspx#bm1169083

I put that into your example plus I added an additional tip: if any of the columns are optional, the comparison you used may not give the desired result. If...

Instead of unpivot you can also use "cross apply" to create a row for each result found. This avoids some costly query plan steps, which can not be avoided when...

Jeff Moden (12/11/2011)

---

I've never found a decent use for it before but this sounds like the perfect use for an "INSTEAD OF" trigger against a view. It would guarantee...