SQL Server XML modify remove nodes without attributes or inner text

Question

I need to be able to delete all nodes with any name as long as (a) the node has no attributes or (b) the node has no inner text.

Sample XML:

<mydoc> <delete_me /> <keep_me value="yes!" /> <but_do_not_keep_me></but_do_not_keep_me> <should_you_keep_me>Absolutely!</should_you_keep_me> </mydoc>

The expected output is

<mydoc> <keep_me value="yes!" /> <should_you_keep_me>Absolutely!</should_you_keep_me> </mydoc>

I've got the queries figured out to satisfy one condition or the other.

@xml.modify('delete //*[empty(@*)]')

This will only keep elements with an attribute. Similarly,

@xml.modify('delete //*[empty(node())]')

will remove all empty nodes. But I cannot figure out how to accomplish both. If I try to do a union |, that gives me the error XQuery [modify()]: The XQuery syntax 'union' is not supported..

Any suggestions?

@ZLK You are a genius. I've been using the | for "and". I had no idea you could use the actual text and to accomplish the same thing. Thank you! — Jarrett Meyer
– Jarrett Meyer, Commented Aug 15, 2017 at 1:22

Jarrett Meyer · Accepted Answer · 2017-08-15 01:38:24Z

1

As per @ZLK's comment above, the and keyword is allowed.

@xml.modify('delete //*[empty(node()) and empty(@*)]')

works perfectly.

answered Aug 15, 2017 at 1:38

Jarrett Meyer

19.6k6 gold badges61 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

SQL Server XML modify remove nodes without attributes or inner text

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