CODEWITHSUNDEEP
sql-serverxml
ca9163d9
ca9163d9

Reputation: 29209

Update xml column in a table

I need to remove a Xml element which has the attribute of A with the value of xxxx from the Xml values in a column.

Method 1:

update t set x = x.query('//E[@A != "xxxx"]')

Method 2:

update t set x.modify('delete /E[@A = "xxxx"]')

Which one is better?

Upvotes: 0

Views: 225

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

Both calls would not do the same:

DECLARE @xml XML=
N'<root>
  <test pos="1" a="xxx">test 1</test> 
  <test pos="2" a="SomeOther">test 2</test> 
  <test pos="3" a="xxx">test 3</test>
  <OtherElement>This is another element</OtherElement> 
  </root>';

--Try this with either this approach

SET @[email protected](N'//test[@a!="xxx"]')

--Or try it with this

SET @xml.modify(N'delete //test[@a="xxx"]')

SELECT @xml;

The result of the first is

<test pos="2" a="SomeOther">test 2</test>

While the second returns

<root>
  <test pos="2" a="SomeOther">test 2</test>
  <OtherElement>This is another element</OtherElement>
</root>

XML is not stored as the text you see. It is stored as a tree structure representing a complex document. To modify this is fairly easy, just kick out some elements. The query()approach has to rebuild the XML and replace the first with an new one. So my clear advise is: Use the modify()approach! If you are really good with XQuery and FLWOR the query() approach is much mightier, but this is another story...

Upvotes: 1

Related Questions