ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Parsing XML stream file and SQLSTATE=10507 message=' ??(item(), item()= )'

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Parsing XML stream file and SQLSTATE=10507 message=' ??(item(), item()= )'

    I've have XML data stored in *STMF with CCSID=1208 as follow
    Code:
         [<CMO_DANE>
             <CMO_PODMIOT>
                  <REF>1234567890</REF>
                 <CMO_R>
                      <N>123456</N>
                 </CMO_R>
               <CMO_R>
                    <N>124</N>
               </CMO_R>
            </CMO_PODMIOT>
           <CMO_PODMIOT>
                <REF>12345678</REF>
               <CMO_R>
                    <N>1236</N>
               </CMO_R>
               <CMO_R>
                    <N>121</N>
               </CMO_R>
           </CMO_PODMIOT>
    </CMO_DANE>[
    I expect list like:
    Code:
    REF                  N
    123456             124
    12345678         1236
    12345678         121
    So, I try to read above *STMF using SQL
    Code:
    [FONT=Calibri][SIZE=10px]select                                                         [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]   xmldta.REF,                                             [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]   xmldta.N                                                  [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]from XMLTABLE('/CMO_DANE/CMO_PODMIOT' PASSING                  [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]xmlparse (document get_xml_file([/SIZE][/FONT][FONT=Calibri][SIZE=10px]'/tmp/data.xml'))   [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]COLUMNS REF CHAR(10) PATH 'REF',                 [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]      N     CHAR(26) PATH 'CMO_R/N'         [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]      ) xmldta    [/SIZE][/FONT]
    and I have received SQL
    Code:
    [FONT=Calibri][SIZE=10px]Message ID . . . . . . :   CPF503E       Severity . . . . . . . :   30        [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]Message type . . . . . :   Diagnostic                                         [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]Date sent  . . . . . . :   28/01/20      Time sent  . . . . . . :   08:52:16  [/SIZE][/FONT]
    
    [FONT=Calibri][SIZE=10px]Message . . . . :   User-defined function error on member QSQPTABL.           [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]Cause . . . . . :   An error occurred while invoking user-defined function    [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]XMLTABLE in library QSYS2. The error occurred while invoking the associated [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]external program or service program CommonUDTF in library QSYS, program     [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]entry point or external name QQCommonUDTF, specific name *N. The error      [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]occurred on member QSQPTABL file QSQPTABL in library QSYS2. The error code  [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]is 1. The error codes and their meanings follow:                            [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]1 -- The external program or service program returned SQLSTATE 10507. The [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]text message returned from the program is: [FONT=Arial]■■[/FONT]( item(), item()+ )[FONT=Arial]■■[/FONT]item() .  [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]2 -- The external program failed before it completed.                     [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]3 -- The database timed out waiting for the program to return. The timeout[/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]value used by the database was 0 minutes and 30 seconds.                    [/SIZE][/FONT]
    but SQL
    Code:
    [FONT=Calibri][SIZE=10px]select                                                         [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]   xmldta.REF                                             [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]from XMLTABLE('/CMO_DANE/CMO_PODMIOT' PASSING                  [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]xmlparse (document get_xml_file([/SIZE][/FONT][FONT=Calibri][SIZE=10px]'/tmp/data.xml'))   [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]COLUMNS REF CHAR(10) PATH 'REF'                 [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]      ) xmldta    [/SIZE][/FONT]
    works.

    Could you help me?
    Any ideas?
    Any advices?

  • #2
    Can you please post your complete (embedded) SQL-Statement in your program starting with EXEC SQL.

    Comment


    • #3
      I found solution. But I can't post o code (error 403).
      So than you for your inspiration

      If this helps someone, I attach a picture with the code
      Click image for larger version

Name:	2020-01-30_152902.jpg
Views:	653
Size:	24.3 KB
ID:	152324
      Attached Files

      Comment

      Working...
      X