Error when using "string-join" in xmltable

Question

I have xml data that looks similar to:

<Document>    
<paymentsts>
         <ref>abc-123</ref>
         <TxSts>RJCT</TxSts>
          <StsRsnInf>
              <Rsn>
                <Cd>FF02</Cd>
              </Rsn>
              <AddtlInf>SyntaxError</AddtlInf>
              <AddtlInf>Error: Invalid Input Character</AddtlInf>
           </StsRsnInf>
    </paymentsts>
</Document>

I am using XMLTABLE to extract the information in a SQL query and I am struggling to get the data from AddtlInf. Because the element can appear multiple times I thought I could use string-join but this is not working. I get the error PL/SQL: ORA-19109: RETURNING keyword expected.

The code to reproduce is below:

declare
  v_xml constant xmltype := xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<Document>
 <paymentsts>
         <ref>abc-123</ref>
         <TxSts>RJCT</TxSts>
          <StsRsnInf>
              <Rsn>
                <Cd>FF02</Cd>
              </Rsn>
              <AddtlInf>SyntaxError</AddtlInf>
              <AddtlInf>Error: Invalid Input Character</AddtlInf>
           </StsRsnInf>
    </paymentsts>
</Document>'
);
 l_info VARCHAR2(500);
begin
  select   errinfo
    into l_info
  from xmltable(
    'Document/paymentsts/StsRsnInf' 
    passing v_xml
    columns
    errinfo varchar2(500) PATH 'string-join(AddtlInf, '','')'
    );

  dbms_output.put_line('(l_info = ' || l_info );
end;

What am I missing?

Answer 1

You could also get the individual values and use listagg to concatenate them:

...
begin
  select listagg(addtlinf, ',') within group (order by ord)
    into l_info
  from xmltable(
    'Document/paymentsts/StsRsnInf/AddtlInf'
    passing v_xml
    columns
    addtlinf varchar2(500) PATH '.',
    ord for ordinality
    );

  dbms_output.put_line('l_info = ' || l_info);
end;

Assuming you want other columns too, you can chain XMLTable calls:

...
begin
  select a.ref, listagg(b.addtlinf, ',') within group (order by ord)
    into l_ref, l_info
  from xmltable(
    'Document/paymentsts'
    passing v_xml
    columns
    ref varchar2(10) PATH 'ref',
    txsts varchar2(10) PATH 'TxSts',
    rsn varchar2(10) PATH 'StsRsnInf/Cd',
    info xmltype PATH 'StsRsnInf/AddtlInf'
    ) a
  cross join xmltable(
    'AddtlInf'
    passing info
    columns
    addtlinf varchar2(500) PATH '.',
    ord for ordinality
    ) b
  group by a.ref;

  dbms_output.put_line('lref = ' || l_ref); 
  dbms_output.put_line('l_info = ' || l_info);
end;

db<>fiddle

Or you could use string-join in the main query XPath to transform the XML before you get to the columns clause.

Answer 2

Misleading error message, I'd say. It is related to your misuse of single quotes for string-join. You should have used double quotes instead of two consecutive single quotes:

No : errinfo varchar2(500) PATH 'string-join(AddtlInf, '','')'

Yes: errinfo varchar2(500) PATH 'string-join(AddtlInf, ",")'

The whole query:

SQL> declare
  2    v_xml constant xmltype := xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
  3  <Document>
  4   <paymentsts>
  5           <ref>abc-123</ref>
  6           <TxSts>RJCT</TxSts>
  7            <StsRsnInf>
  8                <Rsn>
  9                  <Cd>FF02</Cd>
 10                </Rsn>
 11                <AddtlInf>SyntaxError</AddtlInf>
 12                <AddtlInf>Error: Invalid Input Character</AddtlInf>
 13             </StsRsnInf>
 14      </paymentsts>
 15  </Document>'
 16  );
 17   l_info VARCHAR2(500);
 18  begin
 19    select   errinfo
 20      into l_info
 21    from xmltable(
 22      'Document/paymentsts/StsRsnInf'
 23      passing v_xml
 24      columns
 25      errinfo varchar2(500) PATH 'string-join(AddtlInf, ",")'
 26      );
 27
 28    dbms_output.put_line('(l_info = ' || l_info );
 29  end;
 30  /
(l_info = SyntaxError,Error: Invalid Input Character

PL/SQL procedure successfully completed.

SQL>

---

Interestingly, it works with consecutive single quotes if you use the q-quoting mechanism:

errinfo varchar2(500) path q'[string-join(AddtlInf, '', '')]'

Try it.