Enumerating Xml elements and inserting

I have an untyped XML variable that for example holds the following data:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Using SQL DML and\or XQuery I would like to enumerate the XML elements and add an <ExternalId> element to any <Address> element if one doesn't exist with a value of NewId().

So the end result would be:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12334343A</ExternalId>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-333333</ExternalId>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12312312</ExternalId>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Any help would be greatly appreciated.

Regards

Why are you using a unique identifier. Are you familiar with the performance hit that comes with using a unique identifier? I admit there are reasons to want to use a unique identifier and yours may be one of them, but frequently I see unique identifiers used without an understanding of the consequences to performance. Here are some previous threads related to this issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430995&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1544519&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304764&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1493312&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525445&SiteID=1

|||

Thanks for the reply.

Perfomance is not an issue in this instance as it will be run once in a blue moon. But thanks for the heads-up on the performance issues. It doesn't have to be NewID() it can be any globally unique number.

Regards

sql

Enterprise Manager ... inserting <NULL> value.

What is the keyboard shortcut for entering a <NULL> into a database field from within the Enterprise Manager? I've done it before, but can't remember it for the life of me! Also, is there a query that I can use to insert <NULL> in place of ''?Run...as fast as you can ...away from EM...

Just don't put anything in there....

EDIT: It's [CTRL]+0|||Originally posted by Brett Kaiser
It's [CTRL]+0 Thanks!|||Now do yourself a favor and stop using EM and start using QA|||I'm a SQL newbie ... forgive my ignorance. I find it easier to use EM since it's graphical and I can enter/edit information on screen vs. writing queries. Since this DB is in development, this doesn't pose any locking issues.|||Well, if you learn to do it right while you're developing, when you go to production you will have a set of tools for troubleshooting data that you will find yourself going to EM to do otherwise.

You really shouldn't use EM ever for any reason. We continually tell people that because basically the tool sucks. :)