Creating an XML Export File with PeopleSoft


Part of MICA‘s web redesign project is going to involve doing data integration between our CMS (Ingeniux), and our HRMS (PeopleSoft).  Recently I have been playing with XML Publisher, and the flat files that it produces seem so perfect for distribution to our CMS.  I was a little disappointed that the XML documents that XML Publisher creates are not exposed for use as an integration point between systems.

So I began looking for other alternatives.

Integration Broker seems to be quite the rage for the moment.  After poking around with that, it seemed like a lot of work for what would ultimately be a flat file that is updated periodically when things like course descriptions, or degree plans changed.  While this may be useful for more interactive or personalized content, it doesn’t really fit the bill for static content.

The “simplest” (remember, this is PeopleSoft) way I found to create an XML file similar to the one created by XML Publisher is using File Layouts in Application Designer.

The rest of this article is about creating a simple File Layout.

There are 4 basic parts to creating a File Layout.

  1. Create or Select a Record for your data.  I used a view, to avoid having to deal with joins in PeopleCode.
  2. Create a File Layout Definition.
  3. Create an Application Engine Program, with a tiny bit of code in it.
  4. Stuff all these things into a project so you can maintain your sanity at a later time.

After you have selected the record with the data you want to export, create a new File Layout Definition (File->New Definition->File Layout).

New File Layout Definition

On the definition tab, right click “NEW FILE” and select Insert Record.  Search for the record you are using for your data, and double click it.  The Record Definition, and its columns will show up under “NEW FILE” now.  Save your File Layout Definition.

File Layout Definition

Click the Property Icon (Application Designer Property Icon), then click the “Use” tab, and select XML in the “File Layout Format” drop down. Click OK.

File Layout Property \

Create a new Application Engine Program (File->New->Application Engine Program).  Right click “Step01” and select “Insert Action”.  Click where it says “SQL”, and select “PeopleCode” from the drop down.

Application Engine Program New

Double click the new action to edit the PeopleCode.  Copy and paste this PeopleCode into the window:

Local Record &Rec;
Local File &File;
Local SQL &SQL;


&File = GetFile("c:\temp\export_sample.xml", "W", %FilePath_Absolute);
If &File.IsOpen Then
If &File.SetFileLayout(FileLayout.YOUR_FILE_LAYOUT_DEFINITION) Then
&Rec = CreateRecord(Record.YOUR_RECORD_DEFINITION);
&SQL = CreateSQL("%Selectall(:1)", &Rec);
While &SQL.Fetch(&Rec)
&File.WriteRecord(&Rec);
&File.WriteLine("</
YOUR_RECORD_DEFINITION>");
End-While;
Else
Error ("File Layout Not Correct");
End-If;
Else
Error ("file not open");
End-If;
&File.Close();

Save the above Peoplecode and close this window.  Insert all these definitions into your project, and make sure you build it, creating any views and tables you may have created for your data.  Click the Run Program Icon (Application Designer Run Program Icon), and voila: you have a XML file which can be used outside of PeopleSoft (View the File Layout XML Sample)

23 Responses to “Creating an XML Export File with PeopleSoft”

  1. Lynda - October 28, 2008

    Good words.

  2. PStechie - June 3, 2009

    A good one…

  3. Ravi - July 29, 2009

    Good Example and big Thank you. Please keep it.

  4. Mohd - August 4, 2009

    Vow.. Good example to create the XML File. This functionlity is added in PT 8.50(Query to XML File generation). thanks for this information. clients who are on 8.49 have no option other than thsi to generate XML file from PeopleSoft it seems.

  5. Ben Liyanage - September 24, 2009

    We are doing the PT 8.50 upgrade in the next year or so. I hear there is a lot of good functionality and fixes in that upgrade.

  6. Rahul - October 27, 2009

    What about the schema of the xml file ?

  7. jyothi - February 17, 2011

    Please let me know the structure of the Xml
    In my example:

    305

    306
    Srikanth

  8. jyothi - February 17, 2011

    if it a structured xml, please give me the sample xml withparent child relationship

  9. CoreyD - February 8, 2012

    Muchos Gracias, this helped out a lot!

  10. Kirk - April 3, 2012

    I am testing the above code in PeopleTools 8.20. The resulting XML file does not contail encoding=”UTF-8″ in the header tag. How can we add this to the file within the same tag as ?xml version=”1.0″?

  11. Ben Liyanage - April 3, 2012

    If you can’t do this via the File Definition Layout, I suspect you can add it via code right after the ‘End-While;’. It’s been ages since I’ve touched PeopleSoft so I am not sure how much more I can help beyond that.

  12. Kirk - April 3, 2012

    I figured it out. I added the charset parameter in the Get File functional call. If that parameter is omitted, the xml file will be ANSI. To make it unicode, I added the “U” below.

    &File = GetFile(“c:\temp\export_sample.xml”, “W”, “U”, %FilePath_Absolute);

  13. sekhar - November 21, 2012

    good…..

    keep it

  14. Charlie - March 28, 2013

    To reference a question above, I also have a structured xml need. Can I get an example (sample xml with parent child relationship)?

    Thanks

  15. Kishore - June 19, 2013

    I tried the above code.. The app engine shows success and no file is generated. And the record i use has 2 rows in it. Can you guys help me with this please?

  16. Ben Liyanage - June 19, 2013

    Unfortunately I don’t have access to a peoplesoft system these days. I recall peoplesoft not always generating the file immediately. Hopefully this code will get you going in the right direction.

  17. anu - October 10, 2013

    Hi, m getting error saying too many open files when i tried generating pdf for which i am generating xml from file layout for thousands’s of employees.its generaton for few hundreds and den it shws too many file opens.cn u suggest any solution ??

  18. Marl Kens - March 8, 2014

    How’s things, I just noticed that occasionally this page shows an 500 error. I figured you would like to know. All the best

  19. Jed - March 17, 2014

    This has been very helpful! Other forums have discussions about this but most of them failed to include the file layout definition process. Thank much! :)

  20. Aurobindo - May 27, 2014

    Thanks a lot for this post. Worked beautifully.

  21. Sonya - July 7, 2014

    It’s hard to find your posts in google. I found it
    on 14 spot, you should build quality backlinks , it will help you to get more visitors.
    I know how to help you, just type in google – k2 seo tips and tricks

  22. Donna - September 29, 2014

    I have a couple of fields in the file layout that will never contain any data. The person I need to send the XML file to requires that the optional fields be coded with empty tag syntax using one tag as . I created the code described above. Is it possible to change a field from using to using . I didn’t see anywhere i could do this in the file layout or app engine program.

  23. Ben Liyanage - September 30, 2014

    The markup you included is getting stripped out. It’s been quite a while since I have touched any PeopleCode, and I do not have access to an installation to really troubleshoot it. I suspect that you could modify the file after the fact with a little text manipulation, or some xslt.

Leave a Reply