You've made it very hard on yourself by putting spaces in the table and column names. It's not impossible, but it is not according common naming conventions to put these...

Of course I won't put the employee name in the table, I did get that this is just an example. I just took the table from Joe's example and extended...

SQL Kiwi (9/23/2011)

R.P.Rozema (9/23/2011)

Both examples you gave, the break in SSMS and a query timeout, are client-side events. My idea is that shouldn't then their consequences not be handled at the client side...

Assuming I want to store multiple, independent sets in one table. Say, following Joe's example, I need to store the organisational hierarchy for multiple companies. My feeling says I should...

B.T.W. I have created a connect item for the fact that assigning an invalid xml invalidates the transaction, even on an unlogged action. I would appreciate any votes for it:...

SQL Kiwi (9/23/2011)Yes either way is fine. Not sure what you mean by 'uninitialised transaction' though - either a transaction gets started, or it doesn't. Any sane CATCH...

Books Online states that

Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded...

You can run below query on both databases and compare the output using some text comparison tool. Don't forget to set your query output to 'Results to Grid' (Ctrl+D), or...

Hi Jeff, did you find some time to work on some examples for us on nested sets?

LutzM (9/17/2011)

A more detailed description can be found...

You do realise that "adding proper column names" is typical front-end task, don't you. i.e. you shouldn't want to do that in T-SQL.

There is only one way you can 'change'...

It's not duplicates introduced by the splitter that should be eliminated, it's duplicates in the input that need to be removed per the requirement.

Try this for your input:

DECLARE @table...

And add distinct to return only unique values.

edit: sorry, missed the group by

edit 2: the requirement "non duplicate rows" is not met yet. You could try something like this:

SELECT t.ID,...

You are most likely not going to use all information from these different format xml files. Likely you will only use a subset of elements and attributes of each format....