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

i have table of values in sql server 2008 wish insert value xml column within matching row of table. xml column may or may not have tags leading element want insert.

i can achieve through multiple update / xml.modify statements ensure tags exist prior inserting element, seems inefficient , if wanted insert element 5 or 10 tags deep?

here's created example in sql fiddle

the setup have 2 tables (simplified/made here make understandable scenario)

create table tablecolors (id nvarchar(100), color  nvarchar(100)) create table xmltable (id nvarchar(100), xmlcol xml)` 

i need insert element <root><colors><color>tablecolors.color</color></colors></root> xmltable id matches , element doesn't exist. xmlcol can contain many more elements or blank. color tag 0 or many , colors tag 0 or 1.

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

update xmltable set xmlcol.modify(' insert <color>{sql:column("color")}</color> first (/root/colors)[1] ') xmltable inner join tablecolors on xmltable.id = tablecolors.id xmlcol.exist('/root/colors/color[(text()[1]) = sql:column("color")]') = 0  

so, need ensure /root/colors exists before running update statement. please tell me i'm missing , don't have explicitly insert of root (if empty) , insert colors root.

to further explain, here's before , after of inserting new element /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 full example in sql fiddle achieve want, there has better way. missing?

you can include nesting structure in insert statement , 1 update this:

update #xmltable set xmlcol.modify(' insert if (count(/root)=0) <root><colors><color>{sql:column("color")}</color></colors></root>  else (if (count(/root/colors)=0) <colors><color>{sql:column("color")}</color></colors>  else <color>{sql:column("color")}</color>) first  (if (count(/root)=0) (/) else (if (count(/root/colors)=0) (/root) else (/root/colors)))[1]') #xmltable inner join #tablecolors     on #xmltable.id = #tablecolors.id xmlcol.exist('/root/colors/color[(text()[1])=sql:column("color")]') = 0