Using RPGLE embedded SQL to get a source file XML and using XMLPARSE

[TadPole]

HI All,

I am trying to Parse and XML document stored in a source member on the iseries, I want the SQL to parse and place the result in a host variable, then use xquery on that host variable.

something like..

exec sql SELECT XMLPARSE(DOCUMENT GET_XML_FILE('*libl/qxmlsrc(mystest)',1))
INTO :#XMLCLOB
FROM SYSIBM.SYSDUMMY1;

but I keep on getting an error as it cannot find the file, any ideas?

by the way I have commitment control on..

[CRPence]

On 28 Aug 2013 23:28, TadPole wrote:
> I am trying to Parse and XML document stored in a source member on

> the iSeries, I want the SQL to parse and place the result in a host

> variable, then use xquery on that host variable.
>
> something like..
>
> exec sql
> SELECT XMLPARSE(DOCUMENT GET_XML_FILE('*libl/qxmlsrc(mystest)',1))
> INTO :#XMLCLOB
> FROM SYSIBM.SYSDUMMY1
> ;
>

> but I keep getting an error as it cannot find the file, any ideas?

>
> by the way I have commitment control on..

Use the IBM i root file naming. The first argument requires "a path
and file name" which implies root naming. Presumably the string
'/qsys.lib/yourLib.lib/qxmlsrc.file/mystest.mbr' will function as
expected [having replaced "yourlib" with your library name that has the
QXMLSRC file with the MYSTEST member], effecting an open of the named
database source file member, opened just as if it were stream data from
a STMF stored outside the /QSYS.LIB file system. Also, the second
argument specified [the integer value one] seems not to be documented:

IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Built-in functions -> Scalar functions
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscagetxmlfile.htm
_i GET_XML_FILE i_
"The GET_XML_FILE function returns the data from a source stream file or
a source physical file.

Read syntax diagramSkip visual syntax diagram
>>-GET_XML_FILE--(--string-expression--)-----------------------><

_string-expression_

The argument must be a string expression that specifies a path and
file name. The file name may be a name of a source stream file or a
source physical file. For a source physical file, the string must be in
the form 'library/file(member)'.

The result of the function is a BLOB locator.

The function will read the file specified by the argument and convert
the data to UTF-8. If the file does not contains an XML declaration, one
will be added. It will return it as a BLOB locator. The function must be
run under commitment control. The locator will be freed when a COMMIT or
ROLLBACK is performed.
Examples

Register an XML schema document in the XSR registry where the XML schema
is in a source stream file.

CALL XSR_REGISTER ('myschemalib', 'myschema', NULL,
GET_XML_FILE('/home/XML/MySchema.XSD'), NULL)

..."

--
Regards, Chuck

[TadPole]

Yes tried all, changed it to:

SELECT CAST(
GET_CLOB_FROM_FILE('mylib/qxmlsrc(mymember)')
AS CHAR(100))
FROM SYSIBM.SYSDUMMY1

no joy.

SELECT CAST(
GET_CLOB_FROM_FILE('/qsys.lib/mylib.lib/qxmlsrc.file/mymember.mbr')
AS CHAR(100))
FROM SYSIBM.SYSDUMMY1

no joy.

see if it was working from the IFS....

SELECT CAST(
GET_CLOB_FROM_FILE('/somefile.txt')
AS CHAR(100))
FROM SYSIBM.SYSDUMMY1

all OK... just likes the IFS, but I need it to like the file system as I need this to be ok in our change control system.


so it seems that it does not like the source file perhaps I have created..., change it to use the normal QRPGLESRC... still no joy. Make sure commit control on, all OK, make sure source file has journal, all ok..

a bit lost now... is it because there is some IBM type error?

[TadPole]

FULL ERROR IS:

Cause . . . . . : An error occurred while invoking user-defined function GET_CLOB_FROM_FILE in library QSYS2. The error occurred while invoking the associated external program or service program QDBSSUDF2 in library QSYS, program entry point or external name QSQGTCF, specific name GET_CLOB_FROM_FILE_1. The error occurred on member SYSDUMMY1 file SYSDUMMY1 in library SYSIB00033. The error code is 1. The error codes and their meanings follow:
1 -- The external program or service program returned SQLSTATE 42704. The text message returned from the program is: *N in *N type *FILE not found.

[TadPole]

It is because it does not recognize the ASP that the lib is on, if I use QGPL it works ok, will post correction of code later.

[jvo...@gmail.com]

On Friday, August 30, 2013 2:04:23 AM UTC+2, TadPole wrote:
> It is because it does not recognize the ASP that the lib is on, if I use QGPL it works ok, will post correction of code later.

Hi TadPole,

did you solve this problem? I've the same issues :-(

[CRPence]

On 29 Aug 2013 17:04, TadPole wrote:
> On 29 Aug 2013 15:52, TadPole wrote:
>
>> On 8/28/13 11:28 PM, TadPole wrote:
>>
>>> <<SNIP>>

>>>
>>> exec sql
>>> SELECT XMLPARSE(DOCUMENT GET_XML_FILE('*libl/qxmlsrc(mystest)',1))
>>> INTO :#XMLCLOB
>>> FROM SYSIBM.SYSDUMMY1;
>>>
>>> but I keep on getting an error as it cannot find the file, any

>>> ideas? By the way I have commitment control on.

> It is because it does not recognize the ASP that the lib is on, if
> I use QGPL it works ok, will post correction of code later.

Yes. That error indicated that the query was running in the iASP
identified with the 033. I expect... the file that is named, must be in
the same iASP group in which the query request is made [of the file
SYSDUMMY1 in SYSIB00033], or the file must be in the *SYSBAS [e.g. in
QGPL, as noted, functions].

Another possibility, if available [although apparently not allowed in
the context for naming the source file], the three-part naming could be
utilized for a database file. That is, the following request which asks
to query the file from the iASP database, thus the source file name
residing there should be found [or so I expect]:

exec sql
SELECT XMLPARSE(DOCUMENT GET_XML_FILE('*libl/qxmlsrc(mystest)'))
INTO :#XMLCLOB
FROM iASPrdb.SYSIBM.SYSDUMMY1;

Reminder: Recall that "For a source physical file, the string must be
in the form 'library/file(member)'" [regardless I had alluded to using
instead: '/qsys.lib/libname.lib/filename.file/mbrname.mbr']. Given the
name is delimited by apostrophes, apparently the case of the name should
be respected, so 'qgpl/qxmlsrc(mystest)' should probably be specified as
'QGPL/QXMLSRC(MYSTEST)'. If the SQL is accepting the lower-case, and
forcing that name as input to upper-case, that may function only due to
a defect; it depends on whether the mixed-case specification
'QGPL/"MyFile"("SrcTest")' also works as expected. Also the second
argument seemed to have no corresponding parameter... thus why the ",1"
was removed in my revision of the from-clause of the SELECT statement
directing to the RDB that defines the iASP.

--
Regards, Chuck

[dhi...@unitedtechno.com]

Hi All,

I want to send AS400 file to other system by converting to XML using RPG coding. Is there any generic way for converting this for any file in AS/400 by justing giving the file name as input.

Thanks,
Dhivya