I've have XML data stored in *STMF with CCSID=1208 as follow
I expect list like:
So, I try to read above *STMF using SQL
and I have received SQL
but SQL
works.
Could you help me?
Any ideas?
Any advices?
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>[
Code:
REF N 123456 124 12345678 1236 12345678 121
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]
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]
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]
Could you help me?
Any ideas?
Any advices?
Comment