You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Read XML data into PS record using File Layout/App Engine Peoplecode

7 replies [Last post]
Guest's picture
Guest

Hi,

I am reading an XML file using a file layout

I need to insert the data coming in this file into a PS staging record, the tag structure in the XML file has been mapped to the file layout which uses this staging record. However, I fail to understand how to proceed for fetching the data and inserting using App Engine peoplecode. In case of a CSV file, we split the data and pass it into an array for processing, but I am not really sure on what needs to be done on this case, any inputs on this would be of great help

Thanks,
Dan

krisvamsi2005's picture
User offline. Last seen 6 years 38 weeks ago. Offline
Joined: 03/11/2008
Posts: 17
Re: Read XML data into PS record using File Layout/App ...

Make use of createrowset and try writing the rowset data once its fetched..

Thanks,
Vamsi

Thanks,
Vamsi Krishna

Raj's picture
Raj (not verified)
Read XML data into PS record using File Layout/App ...


Function Filename_check_ACC()
   &POS = Find("PSVOUCHER", &Filename_error);
   &POS1 = Find(".", &Filename_error);
   &ErrorFILE_NAME = Substring(&Filename_error, &POS, (&POS1 - 1));
   TC_V_AET.SQL_STATEMENT = &ErrorFILE_NAME | ",";
   &STR_FILENAME = &ErrorFILE_NAME;
End-Function;

Function Move_files()
   If TC_V_AET.ERROR_COUNT > 0 Then
      try
            &COMMAND_STRING = GetEnv("COMSPEC") | " /c " | "  MOVE /Y  " | &In_Path | &STR_FILENAME | " " | &Error_Path | &STR_FILENAME | "_" | %Datetime;
                          &execErr = Exec(&COMMAND_STRING, %Exec_Asynchronous + %FilePath_Absolute);
      catch Exception &ex1
         Exit (1);
      end-try;
   End-If;
   
   If TC_V_AET.ERROR_COUNT = 0 Then
      try
                  &COMMAND_STRING_PROCESSED = GetEnv("COMSPEC") | " /c " | "  MOVE /Y  " | &In_Path | &STR_FILENAME | " " | &Success_Path | &STR_FILENAME | "_" | %Datetime;
         &Processed_ex = Exec(&COMMAND_STRING_PROCESSED, %Exec_Asynchronous + %FilePath_Absolute);
      catch Exception &ex2
         Exit (1);
      end-try;
   End-If;
End-Function;

Function EditRecord(&REC As Record) Returns boolean;
   Local integer &E;
   &REC.ExecuteEdits(%Edit_Required + %Edit_DateRange + %Edit_YesNo + %Edit_TranslateTable + %Edit_PromptTable + %Edit_OneZero);
   REM &REC.ExecuteEdits(%Edit_Required + %Edit_DateRange + %Edit_YesNo + %Edit_OneZero);
   If &REC.IsEditError Then
      For &E = 1 To &REC.FieldCount
         &MYFIELD = &REC.GetField(&E);
         If &MYFIELD.EditError Then
            &MSGNUM = &MYFIELD.MessageNumber;
            &MSGSET = &MYFIELD.MessageSetNumber;
            &LOGFILE.WriteLine("****Record:" | &REC.Name | ", Field:" | &MYFIELD.Name);
            &LOGFILE.WriteLine("****" | MsgGet(&MSGSET, &MSGNUM, ""));           
         End-If;
      End-For;  
      Return False;
   Else
      Return True;
   End-If;
End-Function;

Function ImportSegment(&RS2 As Rowset, &bExecute As boolean) Returns boolean;
   Local Rowset &RS1, &RSP;
   Local string &RecordName;
   Local Record &REC2;
   Local SQL &SQL1;
   Local integer &I, &L;
   &SQL1 = CreateSQL("%Insert(:1)");
   &RecordName = "RECORD." | &RS2.DBRecordName;
   &REC2 = CreateRecord(@(&RecordName));
   For &I = 1 To &RS2.ActiveRowCount
      &RS2(&I).GetRecord(1).CopyFieldsTo(&REC2);
      If (EditRecord(&REC2)) Then
         If (&bExecute = True) Then
            &REC2.PROCESS_INSTANCE.Value = &Total_Count; /*Process instance is used to store the no.of files sucessfully loaded */
            &SQL1.Execute(&REC2);
         End-If;
         For &L = 1 To &RS2.GetRow(&I).ChildCount
            &RS1 = &RS2.GetRow(&I).GetRowset(&L);
            If (&RS1 <> Null) Then
               If (ImportSegment(&RS1, &bExecute) = False) Then
                  Return False;
               End-If;
            End-If;
         End-For;
      Else
         Return False;
      End-If;
   End-For;
   Return True;
End-Function;
/*** Read and Load the data from XML into Temporary Tables. ***/

Local File &FILE1;
Local Record &REC1;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local integer &M;
Local array of string &FNAMES;
Local string &FPath;

&In_Path = GetURL(URL.AP_TC);
&LogFilePath = GetURL(URL.AP_TC_LOG);
&ErrFilePath = GetURL(URL.AP_TC_ERR);
&PrcsdFilePath = GetURL(URL.AP_TC_PRCS);
&filename = GetURL(URL.AP_TC_FLNAME);

&Log_Path = GetURL(URL.AP_TC_LOG);
TC_V_AET.SQL_STATEMENT = &LogFilePath;
&Error_file = &LogFilePath | "\TradeCard" | %Datetime | ".log";
&FNAMES_PSS = FindFiles(&In_Path | "PSVOUCHER*.XML", %FilePath_Absolute);

/*Open the erred data file */
&ErrFileName = "TradeCard_" | %Datetime | ".csv";
&ErrFilePath1 = &ErrFilePath | &ErrFileName;
&Errfile = GetFile(&ErrFilePath1, "W", %FilePath_Absolute);
If &Errfile.IsOpen Then
   &Errfile.WriteLine(%Datetime | ": " | "Begin: error TradeCard report");
Else
   Exit;
End-If;

&LOGFILE = GetFile(&Error_file, "W", %FilePath_Absolute);
&filecount = 0;
&Success_file_count = 0;
&Error_file_count = 0;

/*** For Processing XML files***/

While &FNAMES_PSS.Len > 0
   &FILE1 = GetFile(&FNAMES_PSS.Shift(), "R", "a", %FilePath_Absolute);
   &FILE1.SetFileLayout(FileLayout.TC_VCH);
   &LOGFILE.SetFileLayout(FileLayout.TC_VCH);
   &Filename_error = Upper(&FILE1.Name);
   &RS1 = &FILE1.CreateRowset();
   &RS = CreateRowset(Record.V_HDR_XML_S, CreateRowset(Record.V_LIN_XML_S, CreateRowset(Record.V_DIS_XML_S)));
   &SQL1 = CreateSQL("%Insert(:1)");
   &RS1 = &FILE1.ReadRowset();
   While &RS1 <> Null;
      &filecount = &filecount + 1;
      &Total_Count = &Total_Count + 1;
      &rc = ImportSegment(&RS1, False);
      If (&rc = True) Then
         &rc = ImportSegment(&RS1, True);
         &Success_file_count = &Success_file_count + 1;
         Filename_check_ACC();
      Else
         &Error_file_count = &Error_file_count + 1;
         Filename_check_ACC();
      End-If;
      &RS1 = &FILE1.ReadRowset();
   End-While;
   &FILE1.Close();
   
   If TC_V_AET.ERROR_COUNT > 0 Then
      Move_files();
      &Error_Comment = "****Correct the error in the following path" | &Error_Path | &ErrorFILE_NAME | ", place the corrected file in the root folder(" | &In_Path | ") for reprocessing. Make sure the Root folder should have file name with .XML Extension*****";
      &LOGFILE.WriteLine(&Error_Comment);
      &LOGFILE.WriteRowset(&RS1);
   End-If;
   
   If TC_V_AET.ERROR_COUNT = 0 Then
      Move_files();
      &Error_Comment = "****Following file successfully loaded in Staging table " | &In_Path | &ErrorFILE_NAME | "***********";
      &LOGFILE.WriteLine(&Error_Comment);
   End-If;  
End-While;
TC_V_AET.ERROR_COUNT = &Error_file_count;
TC_V_AET.SUCCESS_COUNT = &Success_file_count;
&LOGFILE.Close();

Swap's picture
Swap (not verified)
Re: Read XML data into PS record using File Layout/App ...

Even I have no idea about this. I am confused coz when I am trying to load the data with XML file, it is not loading. The tag structure in the XML file has been mapped to the file layout, still I am unable to do.

raj.psoft's picture
User offline. Last seen 1 year 32 weeks ago. Offline
Joined: 03/25/2010
Posts: 26
Re: Read XML data into PS record using File Layout/App ...

Can you plz chk the properties of file layout...

Raj

Guest's picture
Guest
Re: Read XML data into PS record using File Layout/App ...

I have multiple tags my xml suchas below:


Unknown
xxx
FIRST ID
9999
Mailing
XXXXXX
XXXXX
IL
XXXXX-XXXX
US


Unknown
YYYY-MM-DD HH:MM:SS
Batch
xxx

Other Information
MMDDYYHHMMSS


Mailing
XXXXXX
XXXXX
IL
XXXXX-XXXX
XX


FIRST ID
9999


SECOND ID
0000000888


I am trying to import the data from the above xml into psoft table. How should the xml file layout look like?Also the filelayout that i created does not populate.
Any help is greatly appreciated.

Guest's picture
Guest
Re: Read XML data into PS record using File Layout/App ...

seems your XML input file might not well-formatted. Please validate XMl file throu xml formatter before processing in PS. Post formatting copy the xml, save .mll file. Put this file in PS server. Run the process. Hope all your rows from input file loaded successfully into staging table.

Guest's picture
Guest
Re: Read XML data into PS record using File Layout/App ...

seems your XML input file might not well-formatted. Please validate XMl file throu xml formatter before processing in PS. Post formatting copy the xml, save .mll file. Put this file in PS server. Run the process. Hope all your rows from input file loaded successfully into staging table.
Sanga