如何摆脱不需要的空XML标记? [英] How can I get rid of unwanted empty XML tags?
本文介绍了如何摆脱不需要的空XML标记?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在下面的SQL XML中，如果所有这些字段均为空，我不想显示recordingInfo标记:
In the below SQL XML I want to not show the recordingInfo tag if all these fields are null:
cc.source_album_title
cc.product_album_promo_title
cc.label
cc.catalogue_no
cc.isrc
这是我当前的查询:
SELECT XMLAGG(XMLElement("Cue" -- start level 5 tag for cue
,XMLFOREST( rownum as "cueId"
,cc.dn_ccst_status as "cueStatusType"
,decode(cc.dn_ccst_status,'5',cc.cup_code,NULL) as "cueCupType" )
,XMLElement("musicWork" -- start level 6 tag for music title
,XMLFOREST(cc.title as "musicTitle")
,XMLElement("recordingInfo" -- start level 7 tag for music title
,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")
)
) -- end level 6 tag for music title
) -- end level 5 tag cue
)
FROM creation_components cc
WHERE cc.prod_cre_surr_id = 22736214
如果上面列出的列全为空，这将生成一个带有空recordingInfo标记的XML:
This generates XML with both an empty recordingInfo tag if the columns listed above are all null:
<Cue>
<cueId>1</cueId>
<cueStatusType>5</cueStatusType>
<cueCupType>W</cueCupType>
<musicWork>
<musicTitle>CLARE@RADIOWORKS.CO.UK</musicTitle>
<recordingInfo></recordingInfo>
</musicWork>
</Cue>
如果其中没有生成其他元素，如何停止包含空的recordingInfo标记的
How can I stop it including the empty recordingInfo tag if there are no other elements generated within it?
推荐答案
您可以使用嵌套的XMLForest(.. as "recordingInfo")调用代替XMLElement("recordingInfo"):
You can use a nested XMLForest(.. as "recordingInfo") call instead of the XMLElement("recordingInfo"):
SELECT XMLAGG(XMLElement("Cue" -- start level 5 tag for cue
,XMLFOREST( rownum as "cueId"
,cc.dn_ccst_status as "cueStatusType"
,decode(cc.dn_ccst_status,'5',cc.cup_code,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 cc
WHERE cc.prod_cre_surr_id = 22736214;
使用伪数据来匹配您的示例输出，并使用XMLSerialise包装器来美化输出:
With dummy data to match your sample output, and an XMLSerialise wrapper to prettify the output:
WITH creation_components (prod_cre_surr_id, dn_ccst_status, cup_code, 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"
,decode(cc.dn_ccst_status,'5',cc.cup_code,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 cc
WHERE 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 non-null data in the later columns:
WITH creation_components (prod_cre_surr_id, dn_ccst_status, cup_code, 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"
,decode(cc.dn_ccst_status,'5',cc.cup_code,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 cc
WHERE 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>
这篇关于如何摆脱不需要的空XML标记?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
