XMLQUERY - insert node when is not null

Question

sample code:

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;
/

What I'm trying to achieve is to add node amount4 only if is not null. This xquery adding empty node ( <amount-3/> ). It is not something that breaks anything, just trying to do not send unnecessary empty nodes.

Mayby someone has some quick, smart solution to this.

Thanks.

Answer 1

Use if .. then .. else .. in the FLWOR expression:

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>,
                     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",
                         NULL 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;
/

Outputs:

<?xml version="1.0"?>
<main-node>
  <node1>
    <node2>
      <node3 product-type="PGZ">
        <id>3</id>
        <amount>1</amount>
        <amount-1>2</amount-1>
        <amount-2>3</amount-2>
      </node3>
    </node2>
  </node1>
</main-node>

fiddle