How to insert an element into xml column not knowing if the tree will already exist?

Question

I have a table of values in SQL Server 2008 from which I wish to insert the value into an XML column within a matching row of another table. The xml column may or may not have all the tags leading up to the element I want to insert.

I can achieve this through multiple update / xml.modify statements to ensure the tags exist prior to inserting the element, but that seems really inefficient and what if I wanted to insert an element 5 or 10 tags deep?

Here's a created example in SQL fiddle

The setup is that I have 2 tables (simplified/made up here to make an understandable scenario)

CREATE TABLE tableColors (id nvarchar(100), color  nvarchar(100))
CREATE TABLE xmlTable (id nvarchar(100), xmlCol xml)`

I need to insert the element <root><colors><color>tableColors.color</color></colors></root> into xmlTable where the id matches and the element doesn't already exist. The xmlCol can contain many more elements or even be blank. The color tag is 0 or many and the colors tag is 0 or 1.

The final statement to insert the element in the right place makes sense, but won't work if the parent tags don't already exist.

UPDATE xmlTable
SET xmlCol.modify(' insert <color>{sql:column("color")}</color> as first into (/root/colors)[1] ')
FROM xmlTable
INNER JOIN tableColors ON xmlTable.id = tableColors.id
WHERE xmlCol.exist('/root/colors/color[(text()[1]) = sql:column("color")]') = 0 

So, I need to ensure /root/colors exists before running this update statement. Please tell me I'm missing something and I don't have to explicitly do an insert of root (if empty) and then insert colors into root.

To further explain, here's a before and after of inserting the new element into /root/colors:

New Element              XML before                                           XML after
<color>blue</color>       -blank-                                              <root><colors><color>blue</color></colors></root>
<color>green</color>      <root><vegitation>yes</vegitation></root>            <root><vegitation>yes</vegitation><colors><color>green</color></colors></root>
<color>white</color>      <root><colors><color>brown</color></colors></root>   <root><colors><color>brown</color><color>white</color></colors></root>

Again, here's a full example in SQL fiddle where I achieve what I want, but there has to be a better way. What am I missing?

Answer 1

You can include the nesting structure in your insert statement and do it with just one update like this:

UPDATE #xmlTable
SET xmlCol.modify('
insert if (count(/root)=0) then <root><colors><color>{sql:column("color")}</color></colors></root> 
else (if (count(/root/colors)=0) then <colors><color>{sql:column("color")}</color></colors> 
else <color>{sql:column("color")}</color>) as first into 
(if (count(/root)=0) then (/) else (if (count(/root/colors)=0) then (/root) else (/root/colors)))[1]')
FROM #xmlTable
INNER JOIN #tableColors
    ON #xmlTable.id = #tableColors.id
WHERE xmlCol.exist('/root/colors/color[(text()[1])=sql:column("color")]') = 0