如何通过XMLQUERY仅在值非空时插入amount4节点？

我尝试实现仅当amount4不为空时才添加<amount-3>节点。当前的XQuery会生成空节点<amount-3/>，虽不影响功能，但希望避免发送此类不必要的空节点。以下是示例代码：

declare
l_xml_response xmltype;
i_cl clob;
l_flag number(1) := 1;
l_product_type varchar2(5) := 'P';
begin
  l_xml_response := xmltype('<?xml version="1.0"?>
<main-node>
  <node1>
    <node2>
      <node3 product-type="PGZ">
        <id>3</id>
      </node3>
    </node2>
  </node1>
</main-node>');

        SELECT  XMLQUERY
                ('
                   copy $res := $req
                   modify insert nodes
                   (
                      <amount>{$amount1}</amount>,
                      <amount-1>{$amount2}</amount-1>,
                      <amount-2>{$amount3}</amount-2>,
                      <amount-3>{$amount4}</amount-3>
                   )
                   after $res/main-node/node1/node2/node3/id
                      
                   return $res
                 '
                 PASSING l_xml_response    AS "req",
                         1    AS "amount1",
                         2    AS "amount2",
                         3    AS "amount3",
                         CASE
                           WHEN NVL(l_flag, 0) = 1 AND l_product_type = 'P' THEN 456
                         END               AS "amount4"
                 RETURNING CONTENT
                )
        INTO    l_xml_response
        FROM    dual;
   
   SELECT  XMLSERIALIZE
            (
              CONTENT l_xml_response
              AS CLOB
              VERSION '1.0'
              INDENT SIZE = 2 
              HIDE DEFAULTS
            )
  INTO    i_cl
  FROM    dual;

  dbms_output.put_line( i_cl);
end;
/

请问是否有简洁巧妙的解决方案？

可以利用XQuery的条件表达式，仅在$amount4不为空时生成<amount-3>节点。修改XMLQUERY中的节点列表部分，用if-then-else逻辑控制节点生成：

SELECT  XMLQUERY
        ('
           copy $res := $req
           modify insert nodes
           (
              <amount>{$amount1}</amount>,
              <amount-1>{$amount2}</amount-1>,
              <amount-2>{$amount3}</amount-2>,
              if ($amount4) then <amount-3>{$amount4}</amount-3> else ()
           )
           after $res/main-node/node1/node2/node3/id
              
           return $res
         '
         PASSING l_xml_response    AS "req",
                 1    AS "amount1",
                 2    AS "amount2",
                 3    AS "amount3",
                 CASE
                   WHEN NVL(l_flag, 0) = 1 AND l_product_type = 'P' THEN 456
                 END               AS "amount4"
         RETURNING CONTENT
        )
INTO    l_xml_response
FROM    dual;

核心说明： #

• if ($amount4) then <amount-3>{$amount4}</amount-3> else ()：当$amount4存在非空值时，生成目标节点；否则返回空序列()，不会插入任何节点。
• 这种方式无需额外PL/SQL逻辑，直接在XQuery内部完成条件判断，简洁高效。

如果需要处理多个可选节点，也可以将所有节点放入序列后用filter过滤空节点，但针对单个节点场景，上述if-then-else是最直接的方案。

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