SQLXML : How to Work With XML Elements (or Nodes) in SQL Server

Assume this is your XML snippet
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

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>

[/sql]

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:

[sql collapse=”false” firstline=”1″ gutter=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]

(/Author/LastName)[1]

[/sql]

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

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

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>
*/
[/sql]

To add an element in a specific position

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

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>
*/
[/sql]

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

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

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>
*/
[/sql]

To delete an element

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
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>
*/
[/sql]

To delete an element based on the element value

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
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>
*/
[/sql]

To delete an element based on the element name

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
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>
*/
[/sql]

Advertisements

10 comments

  • Thank you very much for this wonderful post..
    This had been extremely useful to me. I had been this info for couple of hours and got stuck on this page.
    Best Regards

    Like

  • A great post. I am very new using the XML datatype and is was very useful to have this succinct list of ways to affect the data.
    I did notice that the results for ‘To add an element in a specific position’ did not reflect what is expected. The results are missing the entry.

    Like

  • Amazing job. One of the best sources to work with XML queries.

    Like

  • An excellent post clear & simple way to edit xml with T-Sql

    Like

  • please can any one tell me how to pass variables to the xml for add/update/delete operations. Thanks in advance.

    Like

  • How about renaming the element because a lot of times with XML we might want to keep the element but wan’t to close it. Here is an example:

    Old element with the value
    White

    What we want to change it to

    Or we have to remove the element and add again ?

    Like

  • @hllf twitter API majorly broken on the IOS platform?

    Like

  • Thanks for this valueable site!
    One more question: I need to add a xml-structure into an xml-structure, a kind of

    SET @authorsXML.modify(‘
    insert element Countries {“…”}
    as last into (/Author/Address)[1]

    Best of course using a @variable for the “<tag1…"-part…

    Thanks for any help!!

    Like

  • Great work and useful stuff. In addition, it would be great if you can add some example on how to format (string functions) the value.

    Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s