Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
XMLi anyone???
has no ratings.
Published: 16 Sep 2016
Revised: 22 Mar 2017 - 94 days ago
Last viewed on: 23 Jun 2017 (686 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

XMLi anyone??? Published by: Chris Proctor on 16 Sep 2016 view comments(7)

Happy Friday, everyone! I'm hoping to get some input here. I've never tried to create an XML file on the IFS based on SQL result set, but I've now been asked to do it. I've looked at IBM's DB2 XML Manual and it seems a little confusing to me. I see where you could use XMLELEMENT with a SELECT statement to construct an XML element, but I couldn't quite figure out how I could then write to the XML file.

So, I kept searching the web for a possible easier solution. Well, I found some open source, XMLi, which seems pretty slick to someone who doesn't know what they're doing. Lol. Anyway, I was curious if anyone  has any experience with this software that might be able to answer a question for me. One of my elements has an attribute embedded in it and I'm not sure if that can be done using XMLi. This is what it looks like:

<amount quantity="1">75.00</amount>

I could use xmli_attribute for the attribute and xmli_element for the element, but I'm not sure if they can be embedded. Any suggestions regarding creating an XML file on the IFS would be greatly appreciated. I've been looking into this for a couple of days now.

Thanks!

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: XMLi anyone???
Posted: 9 months 7 days 15 hours 44 minutes ago

The IBM XML stuff is complex at best.

If you are talking about XMLi from this site: https://sourceforge.net/projects/xmli there seems to be XMLi2 which might do what you want--using a pre-existing XML template. Maybe look at that.

For me, I just use SQL iQuery OUTPUT(*XML) which generates the XML into an IFS file for you--adding all the IBM Db2 XML functions for you--but when it comes to custom results, that include things like Attributes, you're best off writing something else. But perhaps XMLi2 will do what you want.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: XMLi anyone???
Posted: 9 months 7 days 15 hours 36 minutes ago

Hi Bob

Thanks for the reply. Yes, you're right. There is XMLi2 and that's actually what I'm trying to set up, the template. It's there where you define all the parts of the XMLi file. That's where I'm not sure about embedding one xmli_* command inside another. I wish I could use SQL iQuery for this, but it's going to a vendor and there are a lot of tags and attrbutes that are needed. frown  Thanks again for the input. I will look into iQuery for possible future use tho.

Posted by: clbirk
Premium member *
Comment on: XMLi anyone???
Posted: 9 months 7 days 11 hours 34 minutes ago

Of course if the stuff seems too complex and you have a decent idea of what you want, you could always write a program to generate the xml off the table/file that you want. 

Something I garnered in 1976 when working on a text editor project while in college using a bells labs book, etc. is that you structure it so that sections do what you want to do.

what I mean by that is,

 

                             movel'<order>'     fldx
                             exsr addon

                             movel order#        fldx

                             exsr chkfld

                             exsr addon

                             movel</order>     fldx

                             exsr addon

etc. etc.

 

And addon would be handling adding this to an array and triggering when to write out what you have and blank out the arry. And chkfld would examine the field so that if it contains non-allowed xml characters, you have the opportunity to convert them to the appropriate code.  The  non-allowed characters in an xml "word" would be & '  " < >    And you simply replace them with the xml entities value (&amp; for &      &lt; for <, etc.).

It is structured in the approach so you can create such. Granted you might want to have a really large array and write it all out in one line or you may decide that you aren't going to have more than say 80 or 100 characters before you go to a new line. 

I had to create xml for our credit card processing years ago, actually when still on an adv/36 or guest/36, and there wasn't any xml tools so I just structured the program up similar to this, and yes there were some of those that had like the quantity=1 stuff and I did that too.

And of course you can be smarter in that if a field is empty you could go either as <ship3></ship3> or do the <ship3/>, with a simple compare and an else statement.

and on numeric fields, quantity=0001 should be fine, but if not, you move the qty field into a character array and you eat up the leading zeros and shift it left, again not difficult at all.

I haven't seen bob's tool for such and that may be the best or easiest way to go, but one can code up xml, write it to a pf and cpytostmf it to the ifs (or you could write directly to ifs).

Unless it is super complex you can code something up in no time at all.

 

chris

 

 

 

                  

 

Posted by: chrisp
Premium member *
Portland, OR
Comment on: XMLi anyone???
Posted: 9 months 7 days 11 hours 8 minutes ago

Hi Chris. Yes, I've also written some XML like that many years ago. I was hoping to use some tool or newer functionality to do it. Thanks for the input.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: XMLi anyone???
Posted: 9 months 6 days 16 hours 13 minutes ago
Edited: Sat, 17 Sep, 2016 at 10:07:09 (280 days ago)

Just FYI, SQL iQuery OUTPUT(*XML) doens't do anything other than add the IBM XML services around the columns. So things like what is now called "attributes" don't apply. I used to have a CPYTOXML tool that was written in RPG, but I no longer support/ship it since OUTPUT(*XML) is pretty neet. But except for one or two clients who use it, I don't find the IBM XML stuff (which is what I'm generating) to be all that intuitive. So best use a specific XML thing instead, I would guess.

For example, the IBM-supplied test file QCUSTCDT when run through SQL iQuery as follows, produces the XML on the IFS.

RUNiQRY 'SELECT * FROM QIWS.QCUSTCDT' OUTPUT(*XML)
<QCUSTCDT><CustomerNumber>938472</CustomerNumber><LastName>Henning</LastName><Initials>G K</Initials><STREET>Main &amp; Elm St</STREET><CITY>Dallas</CITY><STATE>TX</STATE><ZipCode>75217</ZipCode>
<CreditLimit>5000</CreditLimit><CHGCOD>3</CHGCOD><BalanceDue>37.00</BalanceDue><CreditDue>.00</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>839283</CustomerNumber><LastName>Jones</LastName><Initials>B D</Initials><STREET>21 &quot;B&quot; NW 37</STREET><CITY>Clay</CITY><STATE>NY</STATE><ZipCode>13041</ZipCode>
<CreditLimit>400</CreditLimit><CHGCOD>1</CHGCOD><BalanceDue>100.00</BalanceDue><CreditDue>.00</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>392859</CustomerNumber><LastName>Vine</LastName><Initials>S S</Initials><STREET>PO Box 79</STREET><CITY>Broton</CITY><STATE>VT</STATE><ZipCode>5046</ZipCode>
<CreditLimit>700</CreditLimit><CHGCOD>1</CHGCOD><BalanceDue>439.00</BalanceDue><CreditDue>.00</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>938485</CustomerNumber><LastName>Johnson</LastName><Initials>J A</Initials><STREET>3 Alpine Way</STREET><CITY>Helen</CITY><STATE>GA</STATE><ZipCode>30545</ZipCode><CreditLimit>9999</CreditLimit><CHGCOD>2</CHGCOD><BalanceDue>3987.50</BalanceDue><CreditDue>33.50</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>397267</CustomerNumber><LastName>Tyron</LastName><Initials>W E</Initials><STREET>13 Myrtle Dr</STREET><CITY>Hector</CITY><STATE>NY</STATE><ZipCode>14841</ZipCode><CreditLimit>1000</CreditLimit><CHGCOD>1</CHGCOD><BalanceDue>.00</BalanceDue><CreditDue>.00</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>389572</CustomerNumber><LastName>Stevens</LastName><Initials>K L</Initials><STREET>208 Snow Pass</STREET><CITY>Denver</CITY><STATE>CO</STATE><ZipCode>80226</ZipCode><CreditLimit>400</CreditLimit><CHGCOD>1</CHGCOD><BalanceDue>58.75</BalanceDue><CreditDue>1.50</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>846283</CustomerNumber><LastName>Alison</LastName><Initials>J S</Initials><STREET>787 Lake Dr</STREET><CITY>Isle</CITY><STATE>MN</STATE><ZipCode>56342</ZipCode><CreditLimit>5000</CreditLimit><CHGCOD>3</CHGCOD><BalanceDue>10.00</BalanceDue><CreditDue>.00</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>475938</CustomerNumber><LastName>Doe</LastName><Initials>J W</Initials><STREET>59 Archer Rd</STREET><CITY>Sutter</CITY><STATE>CA</STATE><ZipCode>95685</ZipCode><CreditLimit>700</CreditLimit><CHGCOD>2</CHGCOD><BalanceDue>250.00</BalanceDue><CreditDue>100.00</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>693829</CustomerNumber><LastName>Thomas</LastName><Initials>A N</Initials><STREET>3 Dove Circle</STREET><CITY>Casper</CITY><STATE>WY</STATE><ZipCode>82609</ZipCode><CreditLimit>9999</CreditLimit><CHGCOD>2</CHGCOD><BalanceDue>.00</BalanceDue><CreditDue>.00</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>593029</CustomerNumber><LastName>Williams</LastName><Initials>E D</Initials><STREET>485 SE 2 Ave</STREET><CITY>Dallas</CITY><STATE>TX</STATE><ZipCode>75218</ZipCode><CreditLimit>200</CreditLimit><CHGCOD>1</CHGCOD><BalanceDue>25.00</BalanceDue><CreditDue>.00</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>192837</CustomerNumber><LastName>Lee</LastName><Initials>F L</Initials><STREET>5963 Oak St</STREET><CITY>Hector</CITY><STATE>NY</STATE><ZipCode>14841</ZipCode><CreditLimit>700</CreditLimit><CHGCOD>2</CHGCOD><BalanceDue>489.50</BalanceDue><CreditDue>.50</CreditDue></QCUSTCDT>
<QCUSTCDT><CustomerNumber>583990</CustomerNumber><LastName>Abraham</LastName><Initials>M T</Initials><STREET>392 Mill St</STREET><CITY>Isle</CITY><STATE>MN</STATE><ZipCode>56342</ZipCode><CreditLimit>9999</CreditLimit><CHGCOD>3</CHGCOD><BalanceDue>500.00</BalanceDue><CreditDue>.00</CreditDue></QCUSTCDT>

Posted by: Ringer
Premium member *
Comment on: XMLi anyone???
Posted: 7 months 5 days 17 hours 34 minutes ago

Is something like this what you need?

<xmli:for-each>
<Customer ID="${custRow.1}" >
<Name><xmli:value-of select="custRow.2" /></Name>
<Address>
<Street><xmli:value-of select="custRow.3" /></Street> …

Look in the the XML generation section of the Modernization Redbook. 

Posted by: daulat
Comment on: XMLi anyone???
Posted: 3 months 3 days 23 hours 53 minutes ago

Hey.. Chris Proctor .

This is working example and I think this will be helpfull for you.

-----------------------------------------------------------------------------------------------------------------------

0003.00 HALWNULL(*USRCTL) DEBUG(*YES) MAIN(SENDRPTX) DFTACTGRP(*NO) ACTGRP(*NEW)
0003.02 H Option(*nodebugio)
0003.03 H PgmInfo(*PCML:*MODULE)
0003.04 **********
0003.05 D Resp_file S 28A INZ('/QIBM/doc.xml')
0003.06 D ResultXML S 15000A varying
0003.07 D SQLMESSAGE S 3200A varying
0003.08 D RETSCODE S 5P 0
0003.09 D fd S 10I 0
0003.10 D rc S 10I 0
0003.11 *
0003.12 DSENDRPTX PR EXTPGM('SENDRPTX')
0003.13 *
0003.14 * Definition of IFS Open Procedure
0003.15 D open PR 10I 0 extproc('open')
0003.16 D path * value options(*string)
0003.17 D oflag 10I 0 value
0003.18 D mode 10U 0 value options(*nopass)
0003.19 D codepage 10U 0 value options(*nopass)

0003.20 * Definition of IFS Write Procedure
0003.21 D write PR 10I 0 extproc('write')
0003.22 D filedes 10I 0 value
0003.23 D buf * value
0003.24 D nbyte 10U 0 value
0003.25 * Definition of IFS Close Procedure
0003.26 D close PR 10I 0 extproc('close')
0003.27 D filedes 10I 0 value
0003.28 *
0003.29 PSENDRPTX B
0003.30 D PI
0003.31 *
0003.32 *
0003.33 * Need commitment control for working with LOB locators
0003.34 *
0003.35 //
0003.36 d Exc_Cmd PR extpgm('QCMDEXC')
0003.37 d command 200A const
0003.38 d length 15P 5 const
0003.39

0003.40 d $command s 512a
0003.41 //
0003.42 C/Exec SQL
0003.43 C+ Set Option commit=*CHG
0003.44 C/End-Exec
0003.45 /FREE
0003.46 Exec sql declare :ResultXml VARIABLE CCSID 37;
0007.00 Exec sql declare :SQLMESSAGE VARIABLE CCSID 37;
0008.33 EXEC SQL
0008.34 SELECT XMLROW(PORD as "VendoeCode",PPROD as "ItemNumber",
0008.35 PQORD as "QuantityOrdered" Option ROW "HPO" as Attributes)
0008.36 AS XML_ROW into:ResultXML 0008.37 FROM HPO;
0008.38 //
0008.39 //Check for Error
0008.40 IF SQLCOD <> 0;
0008.41 EXEC SQL
0008.42 GET DIAGNOSTICS CONDITION 1 :SQLMESSAGE = MESSAGE_TEXT,
0008.43 :RETSCODE = DB2_RETURNED_SQLCODE;
0008.44 ENDIF;
0008.45 // Write response into a stream file
0008.46 /////////////////////////////////////////////////////////////////////
0008.47 EVAL fd = open(resp_file: 74 : 511);
0008.48 EVAL rc = write(fd:%addr(ResultXML)+2:%len(ResultXML));
0008.49 EVAL rc = close(fd);
0008.50
0008.51 // Program Complete
0009.00 EVAL *INLR = *ON;
0010.00 RETURN;
0011.00 /END-FREE
0012.00
0013.00 P E

---------------------------------------------------------

If you want anything more please let me us.