Remove XML attribute based on value

Question

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?

Yaroslav · Accepted Answer · 2012-07-06 08:09:02Z

3

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.

answered Jul 6, 2012 at 8:09

Yaroslav

6,55210 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Giuseppe Romagnuolo Over a year ago

I know it is an old post but worth updating if things are wrong. I'm confused, wouldn't the suggested answer delete the attribute anyway not only if MobileNumber is Empty? The requester says "The MobileNumber attribute may or may not be there, if it is, and the value is "Empty", i need to change my XML ...", I don't think this is the correct answer then.

Yaroslav Over a year ago

Easy peasy @GiuseppeRomagnuolo post the answer you think is right. Or upvote the one from Mikael.

Giuseppe Romagnuolo Over a year ago

Hi @Yaroslav, my suggestion was more for you to update your answer if you also agreed it is actually misleading. Despite a few years have passed now, there might still be folks struggling with the same issue; your answer, being the accepted one will come up first and be misleading I believe, that's all. I have voted Mikael up but I haven't voted yours down yet :)

Mikael Eriksson · Accepted Answer · 2012-07-06 12:04:27Z

3

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

answered Jul 6, 2012 at 12:04

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

Remove XML attribute based on value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related