从xmltype oracle删除Empty标签 [英] Delete Empty tag from xmltype oracle

问题描述

我想尝试从xmltype中删除空标记.我已经从oracle类型生成了以下xml.在集合中，很少有元素没有值，所以我用空标记生成了.

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.

有人可以帮我吗?

实际输出:

<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>

预期输出:

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

推荐答案

使用DELETEXML并查找XPath //*[not(text())][not(*)]以查找不包含文本和子元素的元素:

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

SQL小提琴

Oracle 11g R2架构设置:

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;

查询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> |

这篇关于从xmltype oracle删除Empty标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！