Delete all empty nodes from XML in SQL Server

Question

I want to remove all empty nodes in a XML file. Even if the node is present as

<Node/>    OR    <Node></Node>

node should be deleted from the XML.

<Root type="1">
<A></A>
<B>
    <B1>
        <B12/>
        <B13/>
    </B1>
    <B2>
        123
        <B21></B21>
    </B2>
   <B3 type="3">
       <B4/>
   </B3>
</B>
<C/>
</Root>

Expected output:

<Root type="1">
<B>
    <B2>
        123
    </B2>
    <B3 type="3">
    </B3>
</B>
</Root>

Delete B1 node because all nodes under B1 is empty and also there is no attribute as well.

Do not delete B2 because , B2 has a value 123 , but delete its empty child.

Do not delete B3 because , B3 has an attribute, but delete its empty child.

I am using SQL to do the same , but in case if this can be done in c# as well , I can call C# script from SSIS, but SQL will be preferred.

Michał Turczyn · Accepted Answer · 2018-07-31 09:59:23Z

1

It can be done easily with regular expressions:

string xml = @"<Root type=""1"">
                < A ></ A >
                < B >
                    < B1 >
                        < B12 />
                        < B13 />
                    </ B1 >
                    < B2 >
                        123
                        < B21 ></ B21 >
                    </ B2 >
                   < B3 type = ""3"" >

                        < B4 />

                    </ B3 >
                 </ B >
                 < C />
                 </ Root > ";


xml = Regex.Replace(xml, @"<.+?/>", "");
xml = Regex.Replace(xml, @"<(.+?)>\s*</\1>", "");

answered Jul 31, 2018 at 9:59

Michał Turczyn

41.3k18 gold badges58 silver badges88 bronze badges

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

2 Comments

KMittal Over a year ago

Do I need to replace all " with "" ?

Michał Turczyn Over a year ago

Yes, because single " would terminate the string, so inside a string you need to use double ".

Ajay Gupta · Accepted Answer · 2018-07-31 11:50:50Z

1

A way to do in C# would be:

var x = XElement.Parse(@"<Root type=""1"">
                            <A></A>
                            <B>
                                <B1>
                                    <B12/>
                                    <B13/>
                                </B1>
                                <B2>
                                    123
                                    <B21></B21>
                                </B2>
                               <B3 type=""3"">
                                   <B4/>
                               </B3>
                            </B>
                            <C/>
                            </Root>");

foreach(XElement child in x.Descendants().Reverse())
{
    if(!child.HasElements && string.IsNullOrEmpty(child.Value) && !child.HasAttributes) 
        child.Remove();
}

edited Jul 31, 2018 at 11:50

answered Jul 31, 2018 at 9:52

Ajay Gupta

1,8651 gold badge14 silver badges22 bronze badges

2 Comments

KMittal Over a year ago

Do I need to replace all " with "" ?

Ajay Gupta Over a year ago

@KMittal yes . .

KMittal · Accepted Answer · 2018-08-01 08:15:10Z

1

The simplest way to do this in SQL Server .

SET @xml.modify('

delete //*[not(node()) and not(./@*)]

');

SELECT @xml.query('//*[not(node()) and not(./@*)]') 

SET @xml.modify('

delete //*[not(node()) and not(./@*)]

');

SELECT @xml.query('//*[not(node()) and not(./@*)]') 

SET @xml.modify('

delete //*[not(node()) and not(./@*)]

');

SELECT @xml.query('//*[not(node()) and not(./@*)]') 

SET @xml.modify('

delete //*[not(node()) and not(./@*)]

');

SELECT @xml.query('//*[not(node()) and not(./@*)]')

I am also able to select all the nodes that I ignored/deleted.

answered Aug 1, 2018 at 8:15

KMittal

6021 gold badge7 silver badges21 bronze badges

Collectives™ on Stack Overflow

Delete all empty nodes from XML in SQL Server

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related