Do several modify operations at once

Question

In the code bellow, first there is a element that needs to be add, them in the rest of the code one attribute named t is created or updated.

declare
  LOG_REFERENCE xmltype:=xmltype('<log  />');
begin

  select  XMLQuery('
    copy $p := $p1 modify insert node <update data="{$p2}" />
              as last into $p/log
    return $p
    ' PASSING LOG_REFERENCE AS "p1", to_char(systimestamp) AS "p2" RETURNING CONTENT)
      INTO LOG_REFERENCE from dual;

  select  XMLQuery('
    copy $i := $p1 modify(
      if (fn:exists($i/log[1]/@t)) then (
        replace value of node $i/log[1]/@t with $p2
      ) else (
        insert node attribute  t {$p2} into $i/log[1]
      )
    )
    return $i
    ' PASSING LOG_REFERENCE AS "p1", to_char(systimestamp) AS "p2" RETURNING CONTENT)
      INTO LOG_REFERENCE from dual;

  dbms_output.PUT_LINE(LOG_REFERENCE.getClobVal());
end;

My problem is that i wanted to join the two modify into one xquery call like this

declare
  LOG_REFERENCE xmltype:=xmltype('<log  />');
begin

  select  XMLQuery('
    copy $p := $p1 modify(
     insert node <update data="{$p2}" />
              as last into $p/log
    )

    copy $i := $p modify(
      if (fn:exists($i/log[1]/@t)) then (
        replace value of node $i/log[1]/@t with $p2
      ) else (
        insert node attribute  t {$p2} into $i/log[1]
      )
    )

    return $i
    ' PASSING LOG_REFERENCE AS "p1", to_char(systimestamp) AS "p2" RETURNING CONTENT)
      INTO LOG_REFERENCE from dual;

  dbms_output.PUT_LINE(LOG_REFERENCE.getClobVal());
end;

In my head this is correct, But obviously im missing something. Or it can't be done in oracle xquery?

the error message that was given:

ORA-19114: XPST0003 - Error during parsing the XQuery expression: LPX-00801: XQuery syntax error at 'copy' 7 copy $i := $p modify( - ^ ORA-06512: em line 5

score 1 · Accepted Answer · answered Jun 19 '17 at 20:28

You can combine both operations in a single modify clause:

declare
  LOG_REFERENCE xmltype:=xmltype('<log  />');
begin

  select  XMLQuery('
    copy $p := $p1 modify(
     insert node <update data="{$p2}" />
              as last into $p/log,
      if (fn:exists($p/log[1]/@t)) then (
        replace value of node $p/log[1]/@t with $p2
      ) else (
        insert node attribute  t {$p2} into $p/log[1]
      )
    )

    return $p
    ' PASSING LOG_REFERENCE AS "p1", to_char(systimestamp) AS "p2" RETURNING CONTENT)
      INTO LOG_REFERENCE from dual;

  dbms_output.PUT_LINE(LOG_REFERENCE.getClobVal());
end;
/

<log t="19-JUN-17 21.25.56.434586 +01:00"><update data="19-JUN-17 21.25.56.434586 +01:00"/></log>

PL/SQL procedure successfully completed.

Which seems to get the same result as your original block, with or without an existing t attribute.

Incidentally, you're currently relying on your NLS session settings to format the timestamp as a string; it would be better to do it explicitly, e.g.

...
    ' PASSING LOG_REFERENCE AS "p1",
        to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF6') AS "p2"
      RETURNING CONTENT)
      INTO LOG_REFERENCE from dual;

to get an output like

<log t="2017-06-19T21:28:54.896506"><update data="2017-06-19T21:28:54.896506"/></log>

Do several modify operations at once

1 Answers1