XML Parser

XML related API has been around for some time now in PeopleSoft and this post is not discussing anything new. Just something that I wanted to pen down for future reference. 

So recently I have been working on a requirement where in I have to parse out a XML response and fetch data values in order to process them further. In this case the same tag element names repeat throughout the XML message and there are multiple levels in the message. The requirement was to traverse each "item" tag and fetch the value for "key" and "value" tags.

Format of the XML message is as shown below.

<?xml version="1.0" encoding="UTF-8"?> <root> <level1> <level2> <level3> <item> <key>FieldName1</key> <value>Value1</value> <type>String</type> </item> <item> <key>FieldName2</key> <value>Value2</value> <type>Date</type> </item> <item> <key>FieldName3</key> <value>Value3</value> <type>Number</type> </item> </level3> </level2> </level1> </root>



Wrote two methods, first one is way longer than the second one.
For both methods I am passing the "field_name_value" and getting back the &fieldvalue value.


Method one - longer.


Local XmlDoc &NewHireXMLDoc;
Local XmlNode &root, &level1, &level2, &level3, &item;
Local number &z, &y, &x, &w, &v;
Local string &sResp, &fieldvalue;
Local boolean &bRet;


rem &sResp is the XML message in string form;
   
&NewHireXMLDoc = CreateXmlDoc();
&bRet = &NewHireXMLDoc.ParseXmlString(&sResp);

&rootNode = &NewHireXMLDoc.DocumentElement;

For &z = 1 To &rootNode.ChildNodeCount
   If &root.GetChildNode(&z).NodeName = "level1" Then
      &level1= &root.GetChildNode(&z);
      For &y = 1 To &level1.ChildNodeCount
         If &level1.GetChildNode(&y).NodeName = "level2" Then
            &level2= &level1.GetChildNode(&y);
            For &x = 1 To &level2.ChildNodeCount
               If &level2.GetChildNode(&x).NodeName = "level3" Then
                  &level3 = &level2.GetChildNode(&x);
                  For &w = 1 To &level3.ChildNodeCount
                     If &level3.GetChildNode(&w).NodeName = "item" Then
                        &item = &level3.GetChildNode(&w);
                        For &v = 1 To &item.ChildNodeCount
                           If &item.GetChildNode(&v).NodeName = "key" And
                                 &item.GetChildNode(&v).NodeValue = "field_name_value" Then
                              &fieldvalue = &item.GetChildNode(&v + 1).NodeValue;
                              &v = &v + 1;
                           End-If;
                       End-For;
                     End-If;
                  End-For;
               End-If;
            End-For;
         End-If;
      End-For;
   End-If;
End-For;



Method two - shorter

Local XmlDoc &NewHireXMLDoc;
Local string &sResp, &fieldvalue;
Local boolean &bRet;

Local array of XmlNode &items;
Local number &y, &z;

rem &sResp is the XML message in string form;
   
&NewHireXMLDoc = CreateXmlDoc();
&bRet = &NewHireXMLDoc.ParseXmlString(&sResp);


&items = &NewHireXMLDoc.DocumentElement.FindNodes("level1/level2/level3/item");
   
   For &z = 1 To &items.Len
      For &y = 1 To &items [&z].ChildNodeCount
         
         If &items [&z].GetChildNode(&y).NodeName = "key" And
               &items [&z].GetChildNode(&y).NodeValue = "field_name_value" Then
            &fieldvalue = &items [&z].GetChildNode(&y + 1).NodeValue;
            Break;
         End-If;
      End-For;

   End-For;

Creating JSON request for REST web services

PeopleSoft provides Document technology to be used to generate JSON request messages but in my experience they are very restrictive especially when working on integrating with 3rd party web services. So following is what I did to generate a JSON request message to post to a 3rd party REST web service. 

The request that I have to generate is in the following form.
[
   {
      "attrib1":"value1",
      "attrib2":"value2",
      "attrib3":{
         "attrib3_1":"values3_1",
         "attrib3_2":"values3_2",
         "attrib3_3":"values3_3",
         "attrib3_4":"values3_4"
      }
   }
]

I am running PT 8.57.x and at this time its not possible to build a document with the root node as an array as shown in the example below. Also I have nested compounds which is also a challenge, the parent compound does not have a label where as the child does. 
So to build something like above I am using the CreateJsonBuilder API provided by PeopleSoft.

Local JsonBuilder &jbldr = CreateJsonBuilder();
Local JsonArray &jArray;

Local string &json;
Local message &request, &response;
Local boolean &bRet;

&jbldr.StartArray(""); /* no label */
 &jbldr.StartObject(""); /* no label */
  &jbldr.AddProperty("attrib1", "value1");
  &jbldr.AddProperty("attrib2", "value2");
   &jbldr.StartObject("attrib3"); /* need a label */
    &jbldr.AddProperty("attrib3_1", "value3_1");
    &jbldr.AddProperty("attrib3_2", "value3_2");
    &jbldr.AddProperty("attrib3_3", "value3_3");
    &jbldr.AddProperty("attrib3_4", "value3_4");
   &jbldr.EndObject("attrib3"); /* closing out the compound or JSONObject */
 &jbldr.EndObject("");
&jbldr.EndArray("");


/* this will return the array just like what I want */
&jArray = &jbldr.GetRootNode().GetJsonObject().GetJsonArray("");
&json = &jArray.ToString();

Created a basic non-rowset based message and assigned that as the request message in my service operation. Use this method to set the content for the message segment for a non-rowset-based message only.

&bRet = &request.SetContentString(&json);
&response = %IntBroker.SyncRequest(&request);

That's it, works like a charm.

Query Transformations

I rarely do much work in PS Query, but while doing some other troubleshooting for PS Query related issues, I noticed the "Transformations" tab in Query Manager. I am running PT 8.56.x in HCM 9.2, but looks like this has been around since 8.53 or 8.54. 

So decided to do a quick test to see what this does and how it works. So the benefit here is that the output can be transformed to create reports right out off the output delivered by PS Query.

So as part of this POC used any existing query. Transforming the output to HTML and using a XSL transformation to highlight the row when a certain condition is matched.

On the Transformation tab within Query Manager, click on Add XSLT which opens a window to key in free-form XSLT text. It would have been nice if there is a wizard of some sort to build the XSLT. 

Gave it a Name and in the "Output File Type" lookup selected HTML. Following are the available choices - 001, A01, ABA, AET, APC, CSS, CSV, D01, DAT, DTD, DTL, ERR, GIF, GIR, HTM, HTML, IDX, JCD, JS, LIS, LOG, MDL, MT9, N01, NVT, NXT, OUT, PS, SQL, STDOUT, SYS, TMP, TRC, TRN, TXT, XLSM, XLSX, XML, XSD, ZIP

Not sure what is the difference between HTM and HTML, but I selected HTML. My sample XSLT is designed as follows.

<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match="/"> <html>
<!-- defined a style for the row that is going to be highlighted -->
<style type="text/css">
tr.redRow td {
font-size: 100%;
font-family: calibri;
font-weight:bold;
background-color: #FF2626;
padding-right: 10px;
color:#000;
}
</style>
<body>
<!-- in the html body defined a html table --> <table border="1">
<!-- Added a heading --> <font color="blue"> <h2>Sample Transformation test</h2> </font>
<!-- defined column headers --> <tr> <th>Employee ID</th> <th>Last Name</th> <th>First Name</th>
<th>Location</th> </tr>
<!-- Here I am selecting the data from PS Query --> <xsl:for-each select="query/row"> <tr>
<!-- defined a if condition to check if -->
<!-- value is McLean then highlight the row --> <xsl:if test="L.DESCR='McLean'"> <xsl:attribute name="class">redRow</xsl:attribute> </xsl:if>
<!-- Output the PS Query results --> <td> <xsl:value-of select="A.EMPLID" /> </td> <td> <xsl:value-of select="C.LAST_NAME" /> </td> <td> <xsl:value-of select="C.FIRST_NAME" /> </td> <td> <xsl:value-of select="L.DESCR" /> </td> </tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>

So saved this and hit the preview button to preview the results. My query uses runtime prompts so those pop'ed up first, provided values for them and results are displayed in a new window (disable pop-up blocker or allow pop-up for this site).

Sample Transformation test

Employee ID	Last Name	First Name	Location
12345	Doe	John	Madrid
13244	Doe	Jane	Egypt
43423	Hufton	Abcde	London
42343	Fleming	Jill	McLean
34423	Tibold	Jozsef	London
67554	Farrell	William	Johannesburg
43556	Myers	Tony	Ireland
75757	Kramer	Nicole	McLean
27341	Wada	Mark	New York
27045	Roger	Wai	McLean
27098	Wayne	Jessica	New York
27125	Garcia	Guillermo	New York
75688	Deepak	Shirguppi	Maryland

If this query is scheduled using Query Scheduler option then a new format of XFORM is available and then I can pick any transformations (can define n number of transformations) that I have defined on the query.