Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XQuery: Deleting Empty Nodes From XML Document Expand / Collapse
Author
Message
Posted Friday, February 28, 2014 5:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 17, 2016 8:30 AM
Points: 4, Visits: 76
Hi peops,

I am new to XQuery and have a problem. I want to delete a child element (in this case called 'step') if and only if it is empty i.e. <step/>. So if there are 3 step elements and the last 2 are empty, then only these 2 elements should be deleted. In effect, the position of this child element is irrelevant. But at moment, I can only delete either all step elements or specified ones. I want to be able to delete the step child element regardless of it's ordinal position. Help any one...please?

Below is the sample document and code.

declare @xdata as xml = '
<root>
<location locationid="10">
<step>Alpha</step>
<step />
<step>Beta</step>
</location>
<location locationid="20">
<step />
<step />
<step>Gamma</step>
</location>
<location locationid="30">
<step />
<step>prime</step>
</location>
</root>
'


set @xdata.modify('delete //step[fn:empty(.) = xs:boolean("true")]')




Many Thanks.
Post #1546265
Posted Friday, February 28, 2014 10:02 AM This worked for the OP Answer marked as solution
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 27, 2016 2:57 AM
Points: 3,026, Visits: 1,777
Hi.. I would use something like the following. This is checking to see if the text node is empty or not in the predicate.:

set @xdata.modify('delete //step[empty(text())]')

Post #1546453
Posted Friday, February 28, 2014 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 17, 2016 8:30 AM
Points: 4, Visits: 76
Many thanks arthurolcot, it worked! I appreciate your effort.
Nice one and have a lovely weekend!

Best Regards.
Post #1546469
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse