CODEWITHSUNDEEP
sqlxmloracle-database
Gaja
Gaja

Reputation: 21

Delete Empty tag from xmltype oracle

i want try to delete the empty tag from xmltype. I Have generate the below xml from oracle type. In the collection few elements does not have values so i generated with empty tag.

Can any one please help me out:

Actual output:

<MESSAGE>
<LOCATIONS>
  <LOCATION_ID>9999</LOCATION_ID>
  <LOC_TYPE>S</LOC_TYPE>
  <NAME>Test Location</NAME> 
  <PHONE_NUM>08 </PHONE_NUM>
   <LAST_MODIFIED_BY/>
   <LAST_MODIFIED_DATE/>
   <POS_CODE/>
</LOCATIONS>
</MESSAGE>

Expected output:

<MESSAGE>
<LOCATIONS>
  <LOCATION_ID>9999</LOCATION_ID>
  <LOC_TYPE>S</LOC_TYPE>
  <NAME>Test Location</NAME> 
  <PHONE_NUM>08 </PHONE_NUM>
</LOCATIONS>
</MESSAGE>

Upvotes: 1

Views: 2808

Answers (2)

Parthiban Oracle APEX
Parthiban Oracle APEX

Reputation: 132

    SELECT
        deletexml(xml_data, '//*[not(text())][not(*)]').getstringval()
    FROM
        (
            SELECT
                xmltype('<MESSAGE>
    <LOCATIONS>
      <LOCATION_ID>9999</LOCATION_ID>
      <LOC_TYPE>S</LOC_TYPE>
      <NAME>Test Location</NAME> 
      <PHONE_NUM>08 </PHONE_NUM>
       <LAST_MODIFIED_BY/>
       <LAST_MODIFIED_DATE/>
       <POS_CODE/>
    </LOCATIONS>
    </MESSAGE>'
                ) xml_data
            FROM
                dual
        )

this is working fine thanks

Upvotes: 0

MT0
MT0

Reputation: 167774

Use DELETEXML and look for the XPath //*[not(text())][not(*)] to find elements that contain no text and no children:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( xml ) AS
SELECT XMLTYPE( '<MESSAGE>
<LOCATIONS>
  <LOCATION_ID>9999</LOCATION_ID>
  <LOC_TYPE>S</LOC_TYPE>
  <NAME>Test Location</NAME> 
  <PHONE_NUM>08 </PHONE_NUM>
   <LAST_MODIFIED_BY/>
   <LAST_MODIFIED_DATE/>
   <POS_CODE/>
</LOCATIONS>
</MESSAGE>' ) FROM DUAL;

Query 1:

SELECT DELETEXML(
         xml,
         '//*[not(text())][not(*)]'
       ).getStringVal()
FROM   table_name

Results:

|                                                                                            DELETEXML(XML,'//*[NOT(TEXT())][NOT(*)]').GETSTRINGVAL() |
|-----------------------------------------------------------------------------------------------------------------------------------------------------|
| <MESSAGE><LOCATIONS><LOCATION_ID>9999</LOCATION_ID><LOC_TYPE>S</LOC_TYPE><NAME>Test Location</NAME><PHONE_NUM>08 </PHONE_NUM></LOCATIONS></MESSAGE> |

Upvotes: 2

Related Questions