ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Converting Physical File Data into XML using RPGLE

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Converting Physical File Data into XML using RPGLE

    Hi,

    Requesting your kind help for the following.

    My Requirement: I want to convert a physical file data into XML using RPGLE. I want a step by step sample please.
    The output data will be placed in IFS folder.

    Data in PF is as follows:
    100012444567
    AAAABBBBCCCC
    DDDDEEEEFFFF


    XML Format:
    <HEADER>
    <REC>
    <TD>1000</TD>
    <MD>1244</MD>
    <FD>4567</FD>
    </REC>
    <REC>
    <TD>AAAA</TD>
    <MD>BBBB</MD>
    <FD>CCCC</FD>
    </REC>
    <REC>
    <TD>DDDD</TD>
    <MD>EEEE</MD>
    <FD>FFFF</FD>
    </REC>
    </HEADER>

    Thanks a lot.

  • #2
    Hi,

    assuming you have a file like this :
    create table pf (
    td char(4),
    md char(4),
    fd char(4) ) ;


    and the following datas :
    insert into pf values ('1000', '1244', '4567') , ('AAAA', 'BBBB', 'CCCC') , ('DDDD', 'EEEE', 'FFFF' ) ;

    Then tou can transform in a single request your SQL datas into XML datas :
    SELECT XMLSERIALIZE(XMLDOCUMENT (
    XMLELEMENT(NAME "HEADER",
    XMLAGG(xmlrow( TD , MD, FD option row "REC" ))
    )) AS CLOB(1M)) AS "xml"
    FROM pf ;



    In a SQLRPGLE program, the easiest way to copy data to IFS is using à XML_CLOB_FILE : https://www.ibm.com/support/knowledg...zajpxmlvar.htm


    Nathanaël

    Comment


    • #3
      Is embedded SQL an option?
      If so it is very easy.
      1. Define an IFS File with the SQLTYPE keyword.
      2. Initialize the subfields of the data structure generated with SQLTYPE
      3. Build the XML data and read the result directly into the IFS File. The hardest part is to build the SQL statement that generates the XML.

      The following example is the complete source code to convert the table YourFile into an XML Document.
      The result is written directly to the IFS into /home/yourDir/YourDir2/YourXMLDoc.xml.
      Code:
             DCL-S YourIFSFile  SQLTYPE(XML_CLOB_File);
             //*********************************************************************************************
               Clear MyXMLDoc;
               MyXMLDoc_Name = '/home/YourDir/YourDir2/YourXMLDoc.xml';
               MyXMLDoc_NL   = %Len(%Trim(MyXMLDoc_Name));
               MyXMLDoc_FO   = SQFOVR;                           //Replace if exists
      
               Exec SQL Select XmlDocument
                                (xmlgroup(Col1, Col2, Col3, .... ColN                                  
                                          Option Row "REC"
                                          Root "HEADER"))
                               into :YourIFSFile
                               From YourFile;
               If SQLCODE < *Zeros;
                  Dsply 'Error occured';
               else;
                  Dsply 'XML Document generated';
               EndIf;
      
             *InLR       = *On;
      BTW I've recently written several functions (and made them opensource) for generating the XML and/or JSON Code for any Table/View/Physical file or for any SQL-Select Statement.


      with this functions the source code can even be reduced as follows:
      Code:
               DCL-S  LocXMLFile      SQLTYPE(XML_Clob_File);                      
               //--------------------------------------------------------------------------------------------
                  //Initialize IFS File
                  Clear LocXMLFile;
                  LocXMLFile_Name = '/home/YourDir/YourDir2/YourXMLDoc.xml';
                  LocXMLFile_NL   = %Len(%Trim(LocXMLFile_Name));                        
                  LocXMLFile_FO   = SQFOVR;                                          //Replace if exists
      
                  //Generate XML and write the result into the IFS
                  Exec SQL  Values(Table2XML('YOURTABLE', 'YOURLIB', '', '', 'HEADER', 'REC'))
                                   into :LocXMLFile;
               If SQLCODE < *Zeros;
                  Dsply 'Error occured';
               else;
                  Dsply 'XML Document generated';
               EndIf;
      
              *INLR = *On;

      Birgitta
      Last edited by B.Hauser; May 6, 2018, 03:55 AM.

      Comment


      • #4
        Thanks a lot Nathanael and Hauser...

        I tried this and it worked fine...which gave me a relief...

        There is change in a requirement, and I need your help for the below.

        PF file is a Flat file.

        The data in the file will be as follows. The first two records belongs to the same customer, the next two belongs to another customer.
        How can convert this into to XML please suggest.

        20000101ABCD ZZZZ
        40000101ABCD XXXX
        20000101EFGH YYYY
        40000101EFGH WWWW


        XML format is as follows.

        <HEADER>
        <LAD>
        <RT>2000</RT>
        <VN>01</VN>
        <AT>01</AT>
        <AN>ABCD</AN>
        <FA></FA>
        <AS>ZZZZ</AS>
        <CON>
        <RT>4000</RT>
        <VN>01</VN>
        <AT>01</AT>
        <AN>ABCD</AN>
        <AB></AB>
        <TL>XXXX</TL>
        </CON>
        </LAD>
        <LAD>
        <RT>2000</RT>
        <VN>01</VN>
        <AT>01</AT>
        <AN>EFGH</AN>
        <FA></FA>
        <AS>YYYY</AS>
        <CON>
        <RT>4000</RT>
        <VN>01</VN>
        <AT>01</AT>
        <AN>EFGH</AN>
        <AB></AB>
        <TL>WWWW</TL>
        </CON>
        </LAD>
        </HEADER>


        Comment


        • #5
          Take a look at this article

          Creating a XML file in the IFS using SQL and RPG to extract the data and write the file


          Comment


          • #6
            Thanks a lot Peder.

            Comment


            • #7
              I just came across this... was looking for alternatives to using CGIDEV2.

              Wondering if I should should follow the example on rpgpgm.com or download the open source tools from Birgitta. Opinions?

              I often need to report the data in a format other than how it is stored in the Table.
              Example:
              Dates are zoned YYYYMMDD - I would like to use real dates
              Some numeric values need to be "00001" instead of just "1"
              Last edited by gwilburn; April 26, 2019, 08:44 AM.

              Comment


              • #8
                I'd go the SQL way! (Independent whether you use my open source tools or not).
                BTW the open source tools are first for those who cannot code RPG.
                I this thread I posted an example how to generate XML data and write them to the IFS with embedded SQL.
                Normally the XML data is not as simple as shown in the example.
                For more complex XML data, you will need multiple CTEs, i.e. first (few) CTEs you build the raw data and then build the elements on the lowest level concatenate and join them in the next CTEs etc until your XML data is complete.

                Converting a date from Dec(8, 0) into a real date with SQL is no problem either: Date(Digits(YourYYYYMMDD) concat '000000') - you may also write an SQL procedure that also handles invalid dates.
                For converting numeric fields right adjusted with leading *Zeros, you may use the VARCHAR_FORMAT() scalar function.
                If you only want to download a table, but with real dates and leading zeros, I'd create a view including all columns you want to extract and additional columns with the converted date and numeric values.

                Birgitta

                Comment


                • #9
                  Birgitta - thanks for the reply. I actually created a basic XML document using the embedded SQL approach. I had to increase the size of the data variables to 16 mb for the data set I'm creating. It was very slick.

                  I think using the SQL option would be very useful in some situations, but (for me, in this situation) using CGIDEV2 and a template approach is quite a bit more convenient. I can easily handle complex XML data that is derived from multiple tables and "decisions".

                  Comment

                  Working...
                  X