Accessing ADP’s XML Engine with PowerShell

Creating my first script to explore the various Data Objects in their entirety was a success I felt and a great experience exploring PowerShell’s XML capability. The experience also taught me quite a bit about .Net’s XML capabilities. I reviewed a few objects, by no means all of them because it gets real dry even for a data hound like me. I reviewed the ServiceSalesClosed, ServiceSalesOpen, ServiceSalesTechHoursOpen, ServiceSalesMLSOpen, ServiceSalesPartsOpen, ServiceSalesDetailsOpen, ServiceSalesDetailsClosed, GL Detail, Customer, Vehicle, and Employee.

During this process one day I had the need to pull some data from the General Ledger Detail. It should have been easy using my script but I ran into problems because I needed to filter the results. In other words I had to use the “Selection” tag in the XML Request. The DAP documentation states that the Selection tag accepts “ENGLISH” statements. ENGLISH seems to be the query language of the PICK DBMS which has been improved or upgraded by IBM (I believe) and is called retrieVe which I think might be part of uniVerse (which I think is what IBM calls PICK these days.) Anyway ADP uses the old ENGLISH it seems, not retrieVe. ENGLISH, as I mentioned in an earlier post, has limited operators and no function building capability. Also no way to join files. PICK has multivalued fields which is a challenge for me. An ENGLISH statement in the selection clause allows for unlimited ANDs and ORs. Here is my first selection clause used on the GL Detail object:


AND WITH ACCTG.DATE.EXT BETWEEN “08/01/10” AND “08/31/10”

The problem with ADP DAP selection clause development is that there is no good source for the Property (Field) Names (called DICT names in ADP speak). The help desk advises you to use an Object exploration tool to look into the “Desc” property of the file on which the object depends and, if the developer described things properly, the DICT name would be there. The problem is that it is not always clear what file the object gathers its information from. Needless to say I invested a significant amount of time searching for the correct DICT name for the property I wanted to filter on. I now have an Excel spreadsheet that houses all my successful ENGLISH statements and what they do for which object.

The above ENGLISH statement says to filter the XML Response to only include GL entries for Account 205 (which would be Contracts in Transit) with an Accounting Date in August 2010. I learned later though that the BETWEEN operator is not inclusive; so I received no 08/01/10 or 08/31/10 entries!

It was very frustrating trying to build successful ENGLISH statements. There are so many wonderful fields available for which I could not find a successful DICT name that I decided that I would need to filter the response after it is returned from the server. This seems inefficient as hell but until I figure a better way to get to these DICT names and, in order to have the most robust data extraction system possible, I need to filter the response……. with XPath….yikes!

As I mentioned before I have never delved this far into XML processing so I reviewed everything I could find on the web about XPath and PowerShell and tested and experimented.

First I developed a script without using Select-XML and then one using it. In the first script which I will develop in this post I used an XPathNavigator object and passed in XPath query strings so here it is:

$Url = http://xxx.yyy.zz.aa:pppp 
$webclient = new-object System.Net.WebClient 
$soapMessage = @" 
<?xml version="1.0" encoding="UTF-8"?> 
<eoapi:Request xmlns:eoapi=http://www.adp.com/xxxxxx version="1.0" ShowUnknownElementErrors="Yes"> 
   <Session> 
   <Connection> 
      <Product>Product</Product> 
      <Server>Server</Server> 
      <Password>password</Password> 
      <Pooling>No</Pooling> 
   </Connection> 
   <GetSet TimeoutSeconds="1800"> 
    <Qbject>Employee</Qbject>
    <Group>DealershipCode</Group> 
    <Selection></Selection> 
    <MultiFlag>E</MultiFlag> 
   </GetSet> 
  </Session> 
</eoapi:Request> 
"@ 
# Upload the Soap payload ($SoapMessage) to the URL:PORT of the ADP XML Engine ($Url)
$result = ([XML]$webclient.UploadString($Url, $soapMessage))
# Create an XPath Object by eliminating the duplicate reply tags starting the XML string at 2nd Reply tag (one with the group attribute), then convert that string to Xml ([xml])
$xpath = [System.XML.XPath.XPathDocument][System.IO.TextReader][System.IO.StringReader]($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('#Run the query and store the OuterXML property of the results in $qryResult
$qryResult = ($navigator.Select($query) | %{$_.OuterXml})
#Add Reply Tag to query result to create Root
$qryResultXML = [xml]('<Reply>'+$qryResult+'</Reply>')
#Date conditions are handled in the pipeline (until I figure out how to convert in the XPath expression) then send required fields to a CSV file.
$qryResultXML.reply.Employee | ?{[dateTime]$_.DateAdded -gt [dateTime]"2010-04-01"} | Select HostItemID, CustNo, NameCode, AccountingAccount, DateAdded, LastUpdate, CustOrCompanyCode, FirstName, MiddleName, LastName, Name1, SSN, CreditLimit, DealerField1, DealerField2, DealerField3, DealerField4, DealerField5, DealerField6, DealerField7, DealerField8, DealerField9, DealerField10 | Export-CSV "C:\Users\John Donnelly\Desktop\Employee.csv" -NoTypeInformation
#Open Excel
$excel = new-object -comobject Excel.Application
#Make Excel visible
$excel.visible = $true
#Open the CSV just created
$workbook = $excel.Workbooks.open("C:\Users\John Donnelly\Desktop\Employee.csv")

Step by step:

In this instance I am perusing the Employee object. The field names I use are fairly common. The first several lines are the same as in the previous post. I use a WebClient object to send a request to the XML Engine and store the results in the $result variable.

I then create an XPathDocument object and pass in the $result.reply.session InnerXML (slightly modified) using the object’s get_InnerXML() method

I then invoke the CreateNavigtor() method on the XPathDocument object and create a navigator object and store it as $navigator.

The fun part was writing a few queries which I have listed with all but one commented out. I could not figure out how to get DateTime to work so I put all DateTime filters in the pipeline later.  In this script the uncommented query pulls all employees who are coded as persons not businesses (interesting distinction eh?) . The date query commented out indicated that I can’t figure out how to write that one. Anyone care to comment?

I then invoke the Select() method of the $navigator object and pass in the query storing the OuterXML of the result in $qryResult. then in order to have an XML object to put in the pipeline I need to give it a root tag, in this case a Reply tag.

Next I select the Employee node like so; $qryResultXML.reply.Employee and pipe that to a foreach to filter out the DateAdded properties we don’t want and then Select the fields we want and Export –CSV.

This took me about 5 hours to put together because of the dearth of PowerShell related discussions about XML and my lack of coding experience.. I really want to thank Dan at PluralSight for his write-ups on XML and PowerShell. They were wonderfully specific enough to help me through this.

This script is somewhat a kluge but it works and will serve as the basis for more refined queries in the future. Thanks for listening and if you see anything strange, please comment and help out those who might rely on my work in the future.

Digg This

Author: John Donnelly

A professional in the automobile business.

2 thoughts on “Accessing ADP’s XML Engine with PowerShell”

  1. I know this is an old post – but on the date time I’ve always selected in ENG using format DDMMMYY – so Oct 1, 2010 would be 01OCT10. I intend on reading the rest of this post as I have been looking for a way to extract data from ADP for a long time. Tired of ‘Enhanced Report Generator’ — RXR was so much better.

    1. Hi Beau,

      Yes the DATE format you mention always works. It is the original PICK Database date format I believe. ADP updated a bit and added an Extended Date type which will also respond to “MM/DD/YY” and even “MM.DD.YY” in most cases.

      I agree RXR and RPX is much better than RPG.

      John

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