PeopleSoft XML File Layout

  1. Create the record to insert data into (for example MY_FILETEST)

     

     

  2. Create file layout by dragging the test record on to the file layout

     

  3. Create XML file to use

 

<?xml version=”1.0″?>

<MY_FILETEST>

    <MYNUMBER>100</MYNUMBER>

    <MY_FACT_BEL>200</MY_FACT_BEL>

</MY_FILETEST>

<MY_FILETEST>

    <MYNUMBER>200</MYNUMBER>

    <MY_FACT_BEL>200</MY_FACT_BEL>

</MY_FILETEST>

<MY_FILETEST>

    <MYNUMBER>300</MYNUMBER>

    <MY_FACT_BEL>200</MY_FACT_BEL>

</MY_FILETEST>

 

 

  1. Save the file in the following folder if running the import from application server if running from the process scheduler save it in the prcs\files folder

    D:\Apps\PT849\appserv\EPM9DMO\files


 

 

  1. Use the following code to import the data into the record

 

Local File &FILE;

Local Record &REC;

Local Rowset &FRS;

 

&FILE = GetFile(“MyFileXML.xml”, “R”);

&REC = CreateRecord(Record.MY_FILETEST);

&SQL = CreateSQL(“%Insert(:1)”);

 

If Not &FILE.IsOpen Then

Error (“TEST: failed file open”);

Else

If Not &FILE.SetFileLayout(FileLayout.MY_FILETEST_XML) Then

Error (“TEST: failed SetFilelayout”);

Else

&FRS = &FILE.ReadRowset();

While &FRS <> Null

&FRS.GetRow(1).MY_FILETEST.CopyFieldsTo(&REC);

&SQL.execute(&REC);

&FRS = &FILE.ReadRowset();

End-While;

End-If;

&FILE.Close();

End-If;

 

  1. Call the code from the page

 

  1. Test if data is inserted

Advertisements

9 Comments on “PeopleSoft XML File Layout”

  1. HH says:

    Re: Webservices.

    Sri,
    Ca you explain webservice security with respect to inbound messages
    How is the userid on the Anonymous node use ? Can a webservice be created such that no userid/password is required t invoke it and how ?
    Thanks for your time.

  2. Tracy says:

    Any chance you can redo the screen shots and make them clearer. Even at 150% magnification, they are unreadable.

  3. Reena says:

    Hello Sri,
    I tried using the method described, but it is unable to load the file. I have several fields in my XML. Do I need to create fields that match all the fields in the following XML file sample. I only need National Id. Basic sample is below



    TO1503_A_200902240951
    2009-02-24T09:51:45-05:00
    Request
    Original


    827034414
    National Student Clearinghouse



    00112200
    HOMETOWN UNIVERSITY

    PRODUCTION


    2009-02-24T09:35:00-05:00


    123456
    111223333

    1985-11-15

  4. Reena says:

    I am sorry but the sample file is not showing up correctly. But my question essentially is that the fie contains TranscriptRequest,Document Id, Organization etc. Do I need to create fields for all these tags?
    Please help!



    TO1503_A_200902240951
    2009-02-24T09:51:45-05:00
    Request
    Original


    827034414
    National Student Clearinghouse



    00112200
    HOMETOWN UNIVERSITY

    PRODUCTION


    2009-02-24T09:35:00-05:00


    123456
    111223333

    1985-11-15


    JANE
    DOE



    1120 CITY PARK AVE.
    D201
    HERNDON
    VA
    20171


    703
    1234567


    requestor@domain.com



    123456
    111223333

    1985-11-15


    JANE
    DOE


    JANE
    SMITH



    HOMETOWN UNIVERSITY
    00112200

    2003-01-01
    2007-12-31
    false

    Bachelor of Science
    2007-01-01



    College of Science

    2003-01-01
    2007-12-31



    School of Management

    2004-01-01
    2007-12-31

    UpdateContacts
    PrepaidFee
    true
    Signature


    57781-1


    UNIVERSITY OF CENTRAL FLORIDA


    UCF COLLEGE OF GRADUATE STUDIES
    230 MILLICAN HALL P.O. BOX 160112
    ORLANDO
    FL
    32816
    OFFICE OF ADMISSIONS


    801
    4458484

    Undergraduate
    GraduateAdmissions
    Mail
    1
    true


    2009-02-24T09:35:00-05:00


    123456
    111223333

    1985-11-15


    JANE
    DOE



    1120 CITY PARK AVE.
    D201
    HERNDON
    VA
    20171


    703
    1234567


    requestor@domain.com



    123456
    111223333

    1985-11-15


    JANE
    DOE


    JANE
    SMITH



    HOMETOWN UNIVERSITY
    00112200

    2003-01-01
    2007-12-31
    false

    Bachelor of Science
    2007-01-01



    College of Science

    2003-01-01
    2007-12-31



    School of Management

    2004-01-01
    2007-12-31

    UpdateContacts
    PrepaidFee
    true
    Signature


    57781-2


    EMU


    116 N. KEDZIE LAB
    SUITE 300
    EAST LANSING
    MI
    48824
    DEPT. OF MATH & SCIENCE EDUCATION

    Undergraduate
    GraduateAdmissions
    Mail
    1
    true


    2009-02-24T09:44:00-05:00


    554488
    222334444

    1978-12-18


    JACK
    MICHAEL
    FROSTER



    220 STUDENT ST
    APT #2205
    HERNDON
    VA
    20171


    703
    2223220


    jfroster@aol.com



    554488
    222334444

    1978-12-18


    JACK
    MICHAEL
    FROSTER



    HOMETOWN UNIVERSITY
    00112200

    true

    Bachelor of Science
    2001-01-01


    Bachelor of Science
    2005-01-01



    College of Education

    1997-01-01
    2001-12-31



    College of Science

    2001-01-01
    2005-12-31



    School of Information Technology and Engineering

    2005-01-01
    2009-12-31

    UpdateContacts
    PrepaidFee
    true
    Signature


    57782-1


    AAA LABORATORIES, INC


    4134 S ALSTON AVE
    SUITE 107
    ANNAPOLIS
    MD
    21072
    BELL MOORISON


    410
    8068800


    AfterDegreeAwarded
    spring
    Bachelor of Science
    2009-01-01

    Graduate
    Employment
    Mail
    1
    true


    2009-02-24T09:50:00-05:00


    000055
    012012012

    1977-08-10


    KATHLEEN
    MIRIAM
    BLOSSOM



    220 GREAT FALLS AVE
    HERNDON
    VA
    20171


    703
    4446477


    kathleen@gmail.com



    000055
    012012012

    1977-08-10


    KATHLEEN
    MIRIAM
    BLOSSOM


    KATHLEEN
    MIRIAM
    PIERCE



    HOMETOWN UNIVERSITY
    00112200

    1996-01-01
    2000-12-31
    false

    Bachelor of Arts
    2000-01-01



    College of Arts and Humanities

    1996-01-01
    2000-12-31

    UpdateContacts
    true
    Signature


    57783-1


    KATHLEEN MIRIAM BLOSSOM


    220 GREAT FALLS AVE
    HERNDON
    VA
    20171

    Undergraduate
    Registrar
    HoldForPickup
    1
    true

    • A G Chetan says:

      Hi Reena,

      Did you got solution for the requirement mentioned above. Please let me know as we have a similar kind of requirement

  5. Reena says:

    Hello Sri,
    I created a very simple XML file now which is as follows(exactly like your example):

    111223333

    Step2: Created a record:T_CLEAR_XML_TBL with one field: NATIONAL_ID
    In File layout field properties
    Length: 20, Field Type: Character, Field Tag: NATIONAL_ID

    Step3: Created AE (code almost same as yours except small changes which log the errors).
    Local File &FILE;
    Local Record &REC;
    Local Rowset &FRS;
    Local SQL &SQL;

    &FILE = GetFile(“C:\temp\transcript_Test1.xml”, “r”, “a”, %FilePath_Absolute);
    &LOGFILE = GetFile(“C:\temp\transcript_Test1.xml.err”, “W”, %FilePath_Absolute);

    &REC = CreateRecord(Record.T_CLEAR_XML_TBL);
    &SQL = CreateSQL(“%Insert(:1)”);
    If Not &FILE.IsOpen Then
    Error (“TEST: failed file open”);
    Else
    &LOGFILE.Writeline(“FileOPEN Successful”);
    If Not &FILE.SetFileLayout(FileLayout.T_CLEARXML_FILELAYOUT) Then
    Error (“TEST: failed SetFilelayout”);

    Else
    &LOGFILE.Writeline(“FileLayout Successful”);
    &FRS = &FILE.ReadRowset();
    &LOGFILE.Writeline(“Ready to read the rowset”);
    &LOGFILE.WriteRowset(&FRS);
    &LOGFILE.Writeline(“before Read loop”);
    While &FRS Null
    &LOGFILE.Writeline(“Reading the data”);
    &FRS.GetRow(1).T_CLEAR_XML_TBL.CopyFieldsTo(&REC);

    &SQL.Execute(&REC);

    &FRS = &FILE.ReadRowset();

    End-While;

    End-If;

    &FILE.Close();

    End-If;

    Step 4: Ran from the client.
    The process seems to run upto the point where I am trying to read the rowset. It does not do teh writeRowset or the comment “before Read loop”).

    Am I supposed to tell some sort of end of file tag?
    Why is it stopping here?
    Please help!
    Thanks

  6. Mike says:

    One thing I had to do to get this to work that wasn’t covered, was to set the File Definition Tag in the FIle Layout Definition Properties Use tab. In the example, MY_FILETEST should be input into that field to get the File Layout to work. Otherwise it was reading null from my file. Once I told the layout what the tag was, it worked.

  7. Thank you says:

    Great useful info….. thanks for sharing….


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s