SQL XML Manipulating XML Nodes / Elements in SQL XML DataType

Assume this is your XML snippet

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SET @authorsXML.modify( '     delete (//*1) ')   /* 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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