CODEWITHSUNDEEP
sqlsql-serverxpathxml-dml
James A Mohler
James A Mohler

Reputation: 11120

Deleting a parent node

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.

Upvotes: 1

Views: 1051

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

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

Upvotes: 2

Scott
Scott

Reputation: 19

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 .......

Upvotes: -1

Related Questions