Transform DB2 to XML from AS/400
I have been requested to create XML files from AS/400 DB2 files to be used to update data on a client's web server. I have looked at IBM's XML Toolkit for iSeries, but the examples are all about parsing XML input and nothing about creating XML from native AS/400 database files. Is there something in the Toolkit that will do this? Does anyone have any experience with DB2 to XML? Thanks.
Software/Hardware used:
Software/Hardware used:
ASKED:
May 10, 2009 3:36 PM
UPDATED:
November 4, 2010 11:38 PM
Answer Wiki:
I have written programs to do what you want to do, and would be happy to provide an example, for you and anyone else who is interested.
You can get me at john.blenkinsop@ne.nykline.com
Sloopy
--------------
DB2 SQL has the capability of creating XML ...
SELECT XML2CLOB(
XMLELEMENT(NAME "DEPARTMENT",
XMLATTRIBUTES (DEPT AS "NAME" ),
XMLAGG(XMLELEMENT(NAME "EMP",
XMLELEMENT(NAME "FIRSTNAME", FNM),
XMLELEMENT(NAME "LASTNAME", LNM) )
ORDER BY LNM )
) ) AS "DEPT_LIST"
FROM TTEST
GROUP BY DEPT;
results in
<DEPARTMENT NAME="BADM "><EMP><FIRSTNAME>CHERYL </FIRSTNAME><LASTNAME>ST......
Happy DB2ing
-------------------------------------------------------------
Er... I do not know who wrote the piece above, regarding SQL generating XML. It was not me. I do have the feeling that it is not available in OS version 5 or below. Probably 6.1.
Sloopy.
=======================================================================
I can highly recommend the program from Sloopy. Its very well written and works after you made your changes according to your needs.
YuVa
================================================================
The portion of the answer above that refers to the XML2CLOB() function is incorrect. The function is from DB2 UDB for LUW. It is not part of DB2 on i nor is it supplied with DB2 XML Extenders for i.
However, DB2 XML Extenders for i can supply a number of functions that can make DB2 and XML easy to work with together.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
Is the XML2CLOb from i6.1? I had never heard of this, and tried it on a V5R4 and it doesn’t seem to work
i have the same problme. Token was not valid
XML2CLOB is for DB2 UDB for Linux and Windows. You can’t use it on iSeries since it doesn’t exist on iSeries. You need to use the functions that are listed in the product documentation. You can’t just take a function from one platform and assume it will work on different hardware under a different operating system.
Try the XMLCLOBFromFile() function on iSeries.
Tom
XML2CLOB is also for DB2 for z/OS from Ver 7 or 8. I made a bad assumption that it exists also on other platforms. Sorry.
I wish there was a clear, public reason why function names couldn’t be matched up. Stuff like this feels like an intentional move to make i stuff more confusing. You need to be experienced in both versions in order to think you know which function to use. I do a lot of guessing.
Tom