Thursday, 31 January 2008

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'' />

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

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

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


No comments: