DevOps, CI/CD and Automation

Hi Friends

from the below XML, its generate by a complex SQL query, Now we need to remove all the object type tags, and all the empty tags.

Version - PL/SQL Release 11.2.0.3.0 - Production

<MajorLine>

    <MAJOR_LINE_OBJECT_TYPE> -- Need to remove these Objects types showing in the XML

        <LINEID>143424538</LINEID>

        <ITEMNAME>TC-ABC-123</ITEMNAME>

        <ITEMPATH>78245</ITEMPATH>

        <QUANTITY>10</QUANTITY>

        <SERVICELINE/> -- Need to remove all the Empty tags

        <MINORLINE>

            <MINOR_LINE_OBJECT_TYPE> -- Need to remove these Objects types showing in the XML

                <LINEID>143424799</LINEID>

                <ITEMNAME>TCC-abc</ITEMNAME>

                <ITEMPATH>78245</ITEMPATH>

                <QUANTITY>10</QUANTITY>

                <SERVICELINE/>-- Need to remove all the Empty tags

                <MINOR_MINOR_LINES>

                    <M_MINOR_LINE_TYPE> -- Need to remove these Objects types showing in the XML

                        <LINEID>143424798</LINEID>

                        <ITEMNAME>SF15-abc-xyz</ITEMNAME>

                        <ITEMPATH>78245</ITEMPATH>

                        <QUANTITY>10</QUANTITY>

                  </M_MINOR_LINE_TYPE>-- Need to remove these Objects types showing in the XML

                </MINOR_MINOR_LINES>

            </MINOR_LINE_OBJECT_TYPE>--Need to remove these Objects types showing in the XML

        </MINORLINE>

    </MAJOR_LINE_OBJECT_TYPE>-- Need to remove these Objects types showing in the XML

</MajorLine>

i tried one of the queries posted in this blog ,PFB  to remove the empty tags , but it didnt work for me unfortunately. Please help.

PFB

WITH sample_data AS (

  SELECT xmltype('<MajorLine>

    <MAJOR_LINE_OBJECT_TYPE>

        <LINEID>143424538</LINEID>

        <ITEMNAME>hijk</ITEMNAME>

        <ITEMPATH>78245</ITEMPATH>

        <QUANTITY>10</QUANTITY>

        <SERVICELINE/>

        <MINORLINE>

            <MINOR_LINE_OBJECT_TYPE>

                <LINEID>143424799</LINEID>

                <ITEMNAME>efg</ITEMNAME>

                <ITEMPATH>78245</ITEMPATH>

                <QUANTITY>10</QUANTITY>

                <SERVICELINE/>

                <MINOR_MINOR_LINES>

                    <M_MINOR_LINE_TYPE>

                        <LINEID>143424798</LINEID>

                        <ITEMNAME>abc</ITEMNAME>

                        <ITEMPATH>78245</ITEMPATH>

                        <QUANTITY>10</QUANTITY>

                    </M_MINOR_LINE_TYPE>

                </MINOR_MINOR_LINES>

            </MINOR_LINE_OBJECT_TYPE>

        </MINORLINE>

    </MAJOR_LINE_OBJECT_TYPE>

</MajorLine>') xmldoc

  FROM dual

)

SELECT deleteXML( t.xmldoc

                , '/MajorLine/*[.="" or contains(.,"?")]'

                ) as result

FROM sample_data t

;

My email ID is sharwitharunthomas@gmail.com -- You can reach me

thanks in advance ... Arun thomas T