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
Comments
Post a Comment