Deleting a parent node

Question

I have have the following xml fragment in a column in SQL Server

<ul>
   <li><a id="cat" href="cat.html">Cat</a></li>
   <li><a id="dog" href="dog.html">Dog</a></li>

I want to delete the <li> tag that has an <a> tag with id="cat"

    DECLARE @id varchar(40) =  'cat'

    UPDATE  dbo.Pref
    SET     xmlPref.modify('delete /ul/li/a[@id=sql:variable("@id")]')
    WHERE   pref    = 1

Does not quite work as it leaves <li> around.

Aleksandr Fedorenko · Accepted Answer · 2013-06-17T07:59:19.253

2

remove the "a" element in the path of expression and add it to the condition

DECLARE @id varchar(40) =  'cat'

UPDATE dbo.Pref
SET xmlPref.modify('delete //ul/li[a/@id=sql:variable("@id")]')
WHERE pref = 1

See demo on SQLFiddle

edited Jun 17 '13 at 07:59

answered Jun 17 '13 at 07:48

Aleksandr Fedorenko

16,594
6
37
44

score -1 · Answer 2 · answered Jun 17 '13 at 07:03

-1

what about using replace built in function within SQL Server

UPDATE dbo.Pref SET COLUMNNAME = REPLACE('EXPRESSION TO BE SEARCHED FOR','PART OF THE STRING TO BE SEARCHED','THE REPLACEMENT STRING') WHERE .......

answered Jun 17 '13 at 07:03

Scott

19
2
6

Deleting a parent node

2 Answers2