CODEWITHSUNDEEP
sqlsqlxml
HarveySaayman
HarveySaayman

Reputation: 351

Remove XML attribute based on value

I have an XML variable with only one element in it. I need to check if this element has a particular attribute, and if it does, i need to check if that attribute has a specific value, and if it does, i need to remove that attribute from the XML element.

So lets say I have 

DECLARE @Xml XML
SET @XML = 
'<person
    FirstName="Harvey"
    LastName="Saayman"
    MobileNumber="Empty"
/>'

The MobileNumber attribute may or may not be there, if it is, and the value is "Empty", i need to change my XML variable to this:

'<person
    FirstName="Harvey"
    LastName="Saayman"
/>'

I'm a complete SQL XML noob and have no idea how to go about this, any ideas?

Upvotes: 2

Views: 4349

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

SET @XML.modify('delete /person/@MobileNumber[. = "Empty"]')

Upvotes: 3

Yaroslav
Yaroslav

Reputation: 6534

Use the modify() DML clause to modify the XML nodes. On this case something like:

SET @XML.modify('delete (/person/@MobileNumber)[1]')

This XML workshop can be helpfull to have a deeper understanding of the DML clauses delete, insert, replace, etc.

Upvotes: 3

Related Questions