Oracle SQL生成指定格式XML时元素与属性构建问题求助

我尝试从表数据生成指定格式的XML，但在正确创建XML元素和属性以匹配预期格式时遇到问题。以下是数据样本查询、预期XML输出以及当前无法得到预期结果的SQL语句，请求技术支持：

数据样本表查询 #

select 'textxml' fileformat,9988335 dept,'SECTION123' startposition,'SECTION123' endposition,'CREATE' action,'MUMBAI' select_for,'ANDHERI' loc,7 Duration
from dual
union ALL
select 'textxml' fileformat,9988335 dept,'Area1' startposition,'Area1' endposition,'CREATE' action,'Pune' select_for,null loc,5 Duration
from dual;

预期XML输出 #

<Data> 
                <Details source="A"> 
                                <pack>COMPNAME</pack>  
                                <ref>textxml</ref>  
                                <dept>9988335</dept> 
                                <MAIN code="TASKCREATE"> 
                                                <StartPosition>SECTION123</StartPosition> 
                                                <EndPosition>SECTION123</EndPosition> 
                                                <Reference Code="Action">CREATE</Reference> 
                                                <Reference Code="Select For">Mumbai</Reference> 
                                                <Reference Code="Location">Andheri</Reference> 
                                                <ReferenceNumber Code="Duration">7</ReferenceNumber> 
                                </MAIN> 
                               <MAIN code="TASKCREATE"> 
                                                <StartPosition>Area1</StartPosition> 
                                                <EndPosition>Area1</EndPosition> 
                                                <Reference Code="Action">CREATE</Reference> 
                                                <Reference Code="Select For">Pune</Reference> 
                                                <ReferenceNumber Code="Duration">5</ReferenceNumber> 
                                </MAIN> 
                                
                </Details> 
</Data> 

当前无法得到预期结果的SQL语句 #

select XMLELEMENT("Data",XMLELEMENT("Details",XMLATTRIBUTES('A' as "sourcesystem"),
XMLAgg(
XMLForest('COMPNAME' as pack, a.fileformat as ref, a.dept as dept
,XMLELEMENT("MAIN",XMLATTRIBUTES('SELECTFOR' as "code")) as test
))))
from (select 'textxml' fileformat,9988335 dept,'SECTION123' startposition,'SECTION123'endposition,'CREATE' action,'MUMBAI'select_for,'ANDHERI' loc,7 Duration
from dual
union ALL
select 'textxml' fileformat,9988335 dept,'Area1' startposition,'Area1'endposition,'CREATE' action,'Pune'select_for,null loc,5 Duration
from dual
)a;

解决方案 #

要生成预期的XML格式，需要调整XML元素的嵌套结构，正确处理属性和空值，以下是修正后的SQL语句：

SELECT XMLELEMENT("Data",
         XMLELEMENT("Details",
           XMLATTRIBUTES('A' AS "source"),
           -- Details下的固定/公共子元素
           XMLELEMENT("pack", 'COMPNAME'),
           XMLELEMENT("ref", MAX(a.fileformat)), -- 所有行fileformat相同，用MAX取唯一值
           XMLELEMENT("dept", MAX(a.dept)), -- 所有行dept相同，用MAX取唯一值
           -- 聚合每个数据行对应的MAIN元素
           XMLAGG(
             XMLELEMENT("MAIN",
               XMLATTRIBUTES('TASKCREATE' AS "code"),
               XMLELEMENT("StartPosition", a.startposition),
               XMLELEMENT("EndPosition", a.endposition),
               XMLELEMENT("Reference", XMLATTRIBUTES('Action' AS "Code"), a.action),
               XMLELEMENT("Reference", XMLATTRIBUTES('Select For' AS "Code"), INITCAP(a.select_for)),
               -- 仅当loc不为空时生成Location对应的Reference元素
               CASE WHEN a.loc IS NOT NULL THEN XMLELEMENT("Reference", XMLATTRIBUTES('Location' AS "Code"), INITCAP(a.loc)) END,
               XMLELEMENT("ReferenceNumber", XMLATTRIBUTES('Duration' AS "Code"), a.duration)
             )
             ORDER BY a.startposition -- 可选：按startposition排序MAIN元素
           )
         )
       ) AS result_xml
FROM (
  select 'textxml' fileformat,9988335 dept,'SECTION123' startposition,'SECTION123' endposition,'CREATE' action,'MUMBAI' select_for,'ANDHERI' loc,7 Duration
  from dual
  union ALL
  select 'textxml' fileformat,9988335 dept,'Area1' startposition,'Area1' endposition,'CREATE' action,'Pune' select_for,null loc,5 Duration
  from dual
) a;

关键修正点： #

• 属性名修正：将Details的属性从sourcesystem改为source，MAIN的code值改为TASKCREATE，匹配预期格式。
• 元素嵌套调整：pack、ref、dept是Details下的公共子元素，不需要放入XMLAgg中；每个数据行对应一个MAIN元素，通过XMLAgg聚合所有MAIN。
• 空值处理：使用CASE语句仅当loc不为空时生成对应的<Reference Code="Location">元素。
• 大小写处理：用INITCAP将select_for和loc的值转换为首字母大写格式，匹配预期输出中的Mumbai、Andheri。
• 公共字段处理：由于所有行的fileformat和dept值相同，使用MAX函数获取唯一值，避免重复生成元素。

内容的提问来源于stack exchange，提问作者Vips Drive1