How to delete nodes containing multiple attributes from XML variable in SQL

Question

I have some XML being passed into a SQL stored procedure that looks like this:

<root>
    <category>
        <property id="blah" otherID="blahblah" year="2015">
        <property id="blah" otherID="notBlahBlah" year="2015">
    </category>
</root>

I want to delete a property node from this using SQL based on multiple node attributes. For example, say I wanted to delete a property with id="blah" and otherID="blahblah". How could I do this? Thank you.

This is the code I have so far based on my best guess of how to do this having read some XQUERY documentation, but it's not working correctly:

DECLARE @XML AS XML
SET @XML = '<root>
               <category>
                   <property id="blah" otherID="blahblah" year="2015">
                   <property id="blah" otherID="notBlahBlah" year="2015">
               </category>
            </root>'

UPDATE @XML.nodes('/root/category/property') 
SET data.modify('delete /root/category/property[id="blah", otherID="blahblah"]')

Please provide sample outcome and what you have tried so far. Here is a link for what is considered necessary for a good SQL question — mtr.web
– mtr.web, Commented Mar 22, 2019 at 17:59
XML support is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Mar 22, 2019 at 19:13

MaxPuissant · Accepted Answer · 2019-03-22 19:00:13Z

1

DECLARE @XML AS XML

SET @XML = '
<root>
               <category>
                   <property id="blah" otherID="blahblah" year="2015"/>
                   <property id="blah" otherID="notblahblah" year="2015"/>
               </category>
            </root>'

SET @XML.modify('delete /root/category/property[@id="blah" and @otherID="blahblah"]')

SELECT @XML

answered Mar 22, 2019 at 19:00

MaxPuissant

314 bronze badges

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

Collectives™ on Stack Overflow

How to delete nodes containing multiple attributes from XML variable in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related