您可以使用嵌套
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>
