如何调整Oracle SQL语句生成符合指定格式的XML输出？

我的查询语句：

SELECT XMLELEMENT("Invoice", XMLAttributes('xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2"' AS "xmlns:xsi", 'xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"' as "xmlns:cac", 'xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"' as "xmlns:cbc", 'xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2"' as "xmlns:ext" ),                        

  XMLAGG(

   XMLELEMENT("Invoice",

    XMLFOREST(

     rc.trx_number AS "cbc:ID",

     rc.trx_date AS "cbc:IssueDate",

     sum(tax_recoverable) AS "tax_recoverable",

     sum(rcg.amount + nvl(tax_recoverable, 0)) AS "net_total"         
    )
   )
  ) 
 ) as resutl

 --INTO  l_xmltype

FROM

  ra_customer_trx_all     rc,

  ra_customer_trx_lines_all  rcl,

  hz_cust_site_uses_all    hzcs,

  ra_cust_trx_line_gl_dist_all rcg,

  gl_code_combinations     cc,

  HZ_CUST_ACCOUNTS RCA , 

  ZX_PARTY_TAX_PROFILE ZPTP,

  HZ_PARTIES HZP1

WHERE

    rc.customer_trx_id = rcl.customer_trx_id

  AND hzcs.site_use_id = rc.bill_to_site_use_id

  AND cc.code_combination_id = rcg.code_combination_id

  AND rcg.customer_trx_id = rc.customer_trx_id

  AND rcl.customer_trx_line_id = rcg.customer_trx_line_id

  and rc.SOLD_TO_CUSTOMER_ID=RCA.CUST_ACCOUNT_ID

  and RCA.party_id=ZPTP.party_id

  and ZPTP.party_id=HZP1.party_id

  AND rcg.customer_trx_line_id IS NOT NULL

  and line_type='LINE'

  --AND rc.customer_trx_id = 8023

group by 

  rc.trx_number,

  HZP1.PARTY_NAME,

  'VAT # '||ZPTP.REP_REGISTRATION_NUMBER,

  rc.trx_date ;

当前XML输出：

<Invoice xmlns:xsi='xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2"' 

     xmlns:cac='xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"' 

     xmlns:cbc='xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"' 

     xmlns:ext='xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2"'>

  <Invoice>

    <cbc:ID xmlns:cbc='xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"'>10002</cbc:ID>

    <cbc:IssueDate xmlns:cbc='xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"'>2019-12-31</cbc:IssueDate>

    <tax_recoverable>4125</tax_recoverable>

    <net_total>86625</net_total>

  </Invoice>

</Invoice>

期望XML输出：

<Invoice xmlns:xsi='xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2"' 

     xmlns:cac='xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"' 

     xmlns:cbc='xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"' 

     xmlns:ext='xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2"'>

  <Invoice>

    <cbc:ID>10002</cbc:ID>

    <cbc:IssueDate>2019-12-31</cbc:IssueDate>

    <cbc:tax_recoverable>4125</cbc:tax_recoverable>

    <cbc:net_total>86625</cbc:net_total>

  </Invoice>

</Invoice>

需要调整SQL语句，使其生成符合期望格式的XML输出。

要实现期望的XML格式，需做两处核心修改：

1. 统一声明命名空间，消除子元素重复声明
   原SQL通过XMLAttributes在根元素定义命名空间，但XMLFOREST生成的带前缀子元素会重复输出命名空间属性。改用XMLNAMESPACES子句统一声明所有命名空间，子元素会自动继承根元素的命名空间定义，无需重复输出。

2. 为自定义字段添加cbc前缀
   将tax_recoverable和net_total修改为cbc:tax_recoverable、cbc:net_total，使其符合期望的命名空间前缀要求。

修改后的完整SQL #

SELECT XMLELEMENT("Invoice",
  XMLAGG(
    XMLELEMENT("Invoice",
      XMLFOREST(
        rc.trx_number AS "cbc:ID",
        rc.trx_date AS "cbc:IssueDate",
        sum(tax_recoverable) AS "cbc:tax_recoverable",
        sum(rcg.amount + nvl(tax_recoverable, 0)) AS "cbc:net_total"
      )
    )
  )
) as result
FROM ra_customer_trx_all rc,
     ra_customer_trx_lines_all rcl,
     hz_cust_site_uses_all hzcs,
     ra_cust_trx_line_gl_dist_all rcg,
     gl_code_combinations cc,
     HZ_CUST_ACCOUNTS RCA,
     ZX_PARTY_TAX_PROFILE ZPTP,
     HZ_PARTIES HZP1
WHERE rc.customer_trx_id = rcl.customer_trx_id
  AND hzcs.site_use_id = rc.bill_to_site_use_id
  AND cc.code_combination_id = rcg.code_combination_id
  AND rcg.customer_trx_id = rc.customer_trx_id
  AND rcl.customer_trx_line_id = rcg.customer_trx_line_id
  AND rc.SOLD_TO_CUSTOMER_ID = RCA.CUST_ACCOUNT_ID
  AND RCA.party_id = ZPTP.party_id
  AND ZPTP.party_id = HZP1.party_id
  AND rcg.customer_trx_line_id IS NOT NULL
  AND line_type = 'LINE'
  --AND rc.customer_trx_id = 8023
GROUP BY rc.trx_number,
         HZP1.PARTY_NAME,
         'VAT # '||ZPTP.REP_REGISTRATION_NUMBER,
         rc.trx_date
XMLNAMESPACES(
  'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS xsi,
  'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
  'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,
  'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext
);

修改说明 #

• XMLNAMESPACES子句在查询末尾统一声明所有命名空间，根元素Invoice会自动包含这些命名空间定义，子元素无需重复输出。
• 调整XMLFOREST中的字段别名，为自定义字段添加cbc:前缀，使其匹配期望格式。
• 移除原XMLELEMENT("Invoice")中的XMLAttributes参数，避免重复定义命名空间。

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