SQL 2005 XQuery Modify

I stumbled across this issue today.

Simply I want to update all rows within my xml, where Organisation="Default", and replace it with the default organisation id.

Unfortunately with .modify in sql you can only modify a single element. The syntax is a little weird but here is my solution.

DECLARE @myXml Xml

SET @myXml = '<Customers>
    <Customer Name=''Fred'' Organisation=''Default''/>
    <Customer Name=''Bob'' Organisation=''Default'' />
</Customers>'

    -- Update Roles
    DECLARE @iNodeCount int
    SET @iNodeCount = @myXml.value('count(/Customers/Customer[@Organisation=''Default''])','int')
    DECLARE @iCurrentNode int
    SET @iCurrentNode = 1


    WHILE (@iCurrentNode <= @iNodeCount)
    BEGIN
        -- Update the root element with the default chargeband
        SET @myXml.modify
        ('
            insert attribute OrganisationId {5}
            into (/Customers/Customer[@Organisation=''Default''][sql:variable("@iCurrentNode")])[1]
        ')

        -- increment the loop
        SET @iCurrentNode = @iCurrentNode + 1
    END

SELECT @myXml