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>
*/

Be Sociable, Share!

• 
•