如何摆脱不需要的空XML标记？

如何摆脱不需要的空XML标记？

您可以使用嵌套

XMLForest(.. as "recordingInfo")

调用代替

XMLElement("recordingInfo")

：

SELECt XMLAGG(XMLElement("Cue"  -- start level 5 tag for cue  ,XMLFOREST( rownum as "cueId"  ,cc.dn_ccst_status as "cueStatusType"  ,depre(cc.dn_ccst_status,'5',cc.cup_pre,NULL) as  "cueCupType" )  ,XMLElement("musicWork"  -- start level 6 tag for music title    ,XMLFOREST(cc.title as "musicTitle")    ,XMLFOREST(      XMLFOREST(cc.source_album_title as "albumTitle"        ,cc.product_album_promo_title as "promoTitle"        ,cc.label as  "label"        ,cc.catalogue_no as "catalogNumber"        ,cc.isrc as "isrc") as "recordingInfo"  -- start level 7 tag for music title      )    )  -- end level 6 tag for music title  )  -- end level 5 tag cue)FROM creation_components ccWHERe cc.prod_cre_surr_id = 22736214;

使用伪数据来匹配您的样本输出，并使用

XMLSerialise

包装器来美化输出：

WITH creation_components (prod_cre_surr_id, dn_ccst_status, cup_pre, title,  source_album_title, product_album_promo_title, label, catalogue_no, isrc) as(  SELECt 22736214, 5, 'W', 'CLARE@RADIOWORKS.CO.UK',    null, null, null, null, null from dual)SELECt XMLSERIALIZE(document  XMLAGG(XMLElement("Cue"  -- start level 5 tag for cue    ,XMLFOREST( rownum as "cueId"    ,cc.dn_ccst_status as "cueStatusType"    ,depre(cc.dn_ccst_status,'5',cc.cup_pre,NULL) as  "cueCupType" )    ,XMLElement("musicWork"  -- start level 6 tag for music title      ,XMLFOREST(cc.title as "musicTitle")      ,XMLFOREST(        XMLFOREST(cc.source_album_title as "albumTitle"          ,cc.product_album_promo_title as "promoTitle"          ,cc.label as  "label"          ,cc.catalogue_no as "catalogNumber"          ,cc.isrc as "isrc") as "recordingInfo"  -- start level 7 tag for music title        )      )  -- end level 6 tag for music title     )  -- end level 5 tag cue  )  as CLOB INDENT SIZE = 2)FROM creation_components ccWHERe cc.prod_cre_surr_id = 22736214;

得到

XMLSERIALIZE(documentXMLAGG(XMLELEMENT("CUE"--STARTLEVEL5TAGFORCUE,XMLFOREST(ROW--------------------------------------------------------------------------------<Cue>  <cueId>1</cueId>  <cueStatusType>5</cueStatusType>  <cueCupType>W</cueCupType>  <musicWork>    <musicTitle>CLARE@RADIOWORKS.CO.UK</musicTitle>  </musicWork></Cue>

在后面的列中使用非空数据：

WITH creation_components (prod_cre_surr_id, dn_ccst_status, cup_pre, title,  source_album_title, product_album_promo_title, label, catalogue_no, isrc) as(  SELECt 22736214, 5, 'W', 'CLARE@RADIOWORKS.CO.UK',    null, null, 'RadioWorks', null, null from dual)SELECt XMLSERIALIZE(document  XMLAGG(XMLElement("Cue"  -- start level 5 tag for cue    ,XMLFOREST( rownum as "cueId"    ,cc.dn_ccst_status as "cueStatusType"    ,depre(cc.dn_ccst_status,'5',cc.cup_pre,NULL) as  "cueCupType" )    ,XMLElement("musicWork"  -- start level 6 tag for music title      ,XMLFOREST(cc.title as "musicTitle")      ,XMLFOREST(        XMLFOREST(cc.source_album_title as "albumTitle"          ,cc.product_album_promo_title as "promoTitle"          ,cc.label as  "label"          ,cc.catalogue_no as "catalogNumber"          ,cc.isrc as "isrc") as "recordingInfo"  -- start level 7 tag for music title        )      )  -- end level 6 tag for music title    )  -- end level 5 tag cue  )  as CLOB INDENT SIZE = 2)FROM creation_components ccWHERe cc.prod_cre_surr_id = 22736214;

额外的标记仍会出现：

XMLSERIALIZE(documentXMLAGG(XMLELEMENT("CUE"--STARTLEVEL5TAGFORCUE,XMLFOREST(ROW--------------------------------------------------------------------------------<Cue>  <cueId>1</cueId>  <cueStatusType>5</cueStatusType>  <cueCupType>W</cueCupType>  <musicWork>    <musicTitle>CLARE@RADIOWORKS.CO.UK</musicTitle>    <recordingInfo>      <label>RadioWorks</label>    </recordingInfo>  </musicWork></Cue>