Assume this is your XML snippet

DECLARE @authorsXML XML

SET @authorsXML = '
<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <FirstName>Johnson</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
    <State>CA</State>
  </Address>
</Author>
'

Note that the examples below show how you can manipulate XML nodes – but most operations require singleton values. Ie, the changes must affect one and only one node. Thus in most the examples we specify the index of the node we want to target.

For example:

(/Author/LastName)[1]

which means we are only targetting the first instance of LastName under the Author node. If you need to do a mass update, you may need to use a cursor.

To add an element as the last node

SET @authorsXML.modify('
    insert element Country {"Canada"} as last into
    (/Author/Address)[1]
')
/*
result:

<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <FirstName>Johnson</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
    <State>CA</State>
    <Country>Canada</Country>
  </Address>
</Author>
*/

To add an element in a specific position

SET @authorsXML.modify('
    insert element MiddleInitial {"A"} after
    (/Author/LastName)[1]
')

/*
result:

<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <FirstName>Johnson</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
    <State>CA</State>
  </Address>
</Author>
*/

To update an element’s value based on a variable value

DECLARE @NewFirstName VARCHAR(20)
SET @NewFirstName = 'Johnny'
SET @authorsXML.modify(
'
    replace value of (/Author/FirstName/text())[1]
    with sql:variable("@NewFirstName")
')

/*
result:
<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <MiddleInitial>A</MiddleInitial>
  <FirstName>Johnny</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
    <State>CA</State>
    <Country>Canada</Country>
  </Address>
</Author>
*/

To delete an element

SET @authorsXML.modify(
'
    delete (/Author/MiddleInitial)
')

/*
result:
<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <FirstName>Johnny</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
    <State>CA</State>
    <Country>Canada</Country>
  </Address>
</Author>
*/

To delete an element based on the element value

SET @authorsXML.modify(
'
    delete (//*[text()="Canada"])
')

/*
result:
<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <FirstName>Johnny</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
    <State>CA</State>
  </Address>
</Author>
*/

To delete an element based on the element name

SET @authorsXML.modify(
'
    delete (//*[local-name()="State"])
')

/*
result:
<Author>
  <ID>172-32-1176</ID>
  <LastName>White</LastName>
  <FirstName>Johnny</FirstName>
  <Address>
    <Street>10932 Bigge Rd.</Street>
    <City>Menlo Park</City>
  </Address>
</Author>
*/

Related posts:

1. SQLXML : How to Merge Two Nodes Using FOR XML PATH ...
2. SQLXML : How to List Schema Elements and Attributes ...
3. Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas ...
4. Learning SQLXML on SQL Server 2005 – series of tutorials ...
5. SQLXML : How to Join Multiple XML Snippets (using query() and UNION ALL) ...
6. SQLXML : How to Create an XML Schema ...