Update a SQL table’s XML value to remove nodes

CodeMinder: to remind myself of stuff that I usually have to look up each time I do it.

To delete all PersonAlternativeName nodes from XmlColumn of the People table:

<span style="color: #0000ff;">with</span><span style="color: #000000;"> xmlnamespaces(
    </span><span style="color: #ff0000;">'</span><span style="color: #ff0000;">http://mydomain.com/person/extension/0.1</span><span style="color: #ff0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> px)
</span><span style="color: #0000ff;">update</span><span style="color: #000000;"> People
    </span><span style="color: #0000ff;">set</span><span style="color: #000000;"> XmlColumn.modify(</span><span style="color: #ff0000;">'</span><span style="color: #ff0000;">delete //px:PersonAlternateName</span><span style="color: #ff0000;">'</span><span style="color: #000000;">)</span>

This entry was posted in CodeMinder, Programming, SQL, XML. Bookmark the permalink.