SQL Server and XML DML

Arthur Olcot has a great blog that covers SQL Server and focuses on optimization and SQLXML. He has a post that lists examples of using XML DML that I found to be incredibly useful. Specifically, the conditional insert example is something that I was able to borrow from for a task at work.  

I needed to add some elements to XML in a bunch of rows of data, and I only wanted to add the elements if they didn't already exist. I was afraid that I might need to do a delete, and then an insert, to make sure that I would end up with duplicate values. Thankfully you can do conditional inserts, and his example made it very easy to understand how to use the conditional.

The following is an example of how to use the conditional insert.

DECLARE @xml XML

SET @xml = '
  Cat 1
  Cat 2
  Cat 3
'

SELECT 'before where Cat 3 already exists', @xml

SET @xml.modify('
 insert (
  if (//category[contains(., "Cat 3")]) then () 
  else element category{"Cat 3"} 
 ) as last into (categories[1])
')

SELECT 'after', @xml


SET @xml = '
  Cat 1
  Cat 2
'

SELECT 'before where Cat 3 does not exist', @xml

SET @xml.modify('
 insert (
  if (//category[contains(., "Cat 3")]) then ()
  else element category{"Cat 3"} 
 ) as last into (categories[1])
')

SELECT 'after', @xml

Thanks, Arthur!