Search code examples
sqlsqlxml

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?

Solution

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