Using PowerShell to Request, Filter and Manipulate an [XML] SOAP Response

PowerShell’s Select-Xml commandlet is used to filter a SOAP Response.

I’m back after a few weeks of car business busyness. I think it is important to post some alternatives to the initial script I wrote to pull data off the ADP DAP Server. When I began this project I didn’t do a lot of research and I thought the Select-XML commandlet was some 3rd party script and not part of PowerShell 2.0. I wanted my script to be usable without any 3rd party snap-ins or functions so I used the XPathDocument method called Navigator instead of Select-XML. Last time I presented my discoveries as it related to using those .net objects.

This time I will show you the script I wrote using Select-XML which is a little cleaner. There were some modifications of the XML output because of differences in the way the commandlet works compared to using the XPathDocument object.

The script has the same XPath query options and for now, I just uncomment the one I want to use and comment out the rest. I have added some Excel formating statements to help make the output more readable.

#With this Script we pull the Employee Object into Xml and we have three places we can set conditions:
#   1) In the $soapMessage Selection Statement 
#   2) In the $query XPath expression statement - uncomment the one you want or make a new one
#   3) In the Pipeline (line 37 or 39) where we select the Employee node and restrict by DateAdded (Need to do this until I figure out how to convert strings to dates in XPath Expression)
$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))
#Write a XPath queries - Uncomment the one you want to use, comment the one's you don't
#$query = "/*/Employee" # This query returns all Employees in the object
$query = "/*/Employee[CustOrCompanyCode='PR']" # This query returns all Employees in the object that show as Persons as opposed to 'BS' for businesses
#$query = "/*/Employee[FirstName='DAVID']" # This query returns all Employees whose first name is 'DAVID' (case matters)
#$query = "/*/Employee[[dateTime]DateAdded > [dateTime]'2010-04-01']" # DOES NOT work....still Working On This One
$qryResult = Select-XML -content ($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('<Reply group='))}) -xpath $query | %{"<Employee>"+$_.node.InnerXML+"</Employee>"}
$qryResultXML = [xml]("<Reply>"+$qryResult+"</Reply>")
#Uncomment the following and comment out the next statement if you have pipeline conditions
#$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
#Uncomment the following and comment out the preceding if you have no pipeline conditions
$qryResultXML.Reply.Employee | 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")
#Get the first sheet by name
$sheet1 = $workbook.worksheets | where {$_.name -eq "Employee"}
#Make first row bold
$range = $sheet1.range("A1:w1")
$range.font.bold = "true"
#Put a border around it
[void]$range.borderAround(1,3)
#Format the various field columns
$sheet1.columns.item('m').NumberFormat = "#,##0.00_);(#,##0.00)" # for numbers
$sheet1.columns.item('l').NumberFormat = "000-00-0000" # for telephone numbers
$sheet1.columns.item('e').NumberFormat = "mm/dd/yy;@" # for dates
$sheet1.columns.item('f').NumberFormat = "mm/dd/yy;@"
$sheet1.columns.item('b').NumberFormat = "@" # for general (makes all entries nicely left justified even if a number)
#Select the Range in use
$WorkBookNew = $sheet1.UsedRange
#Autofit all the columns
[void]$WorkBookNew.EntireColumn.AutoFit()

Here’s my thought process building this rather inelegant solution:

The SOAP request, response variabe and XPath query lines are the same as last post. The major difference is that I am now using the Select-Xml commandlet to filter the SOAP response. Granted this isn’t the most efficient way to select the data. If PICK’s ENGLISH database query language was half as robust as XPath’s I would skip the Select-Xml statement all together and filter using the SELECTION tag of the request.

The power and new part of the script is this monster line that loads the $qryResult variable. We use the Select-Xml commandlet with the -content and -xpath parameters in use. The -content parameter requires a valid xml object so we use the get_InnerXML() method of the XMLDocument object $result.reply.session and strip off the extra “Reply” tag as in the previous post. The -xpath parameter holds a valid XPath query string and I list several examples. The one that is uncommented selects only those Employee nodes where CustOrCompanyCode=’PR’ meaning the employee is a person as opposed to a business (‘BS’). Go figure.

There’s more to this line but here’s the line to look at before we go on:

$qryResult = Select-XML -content ($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('<Reply group='))}) -xpath $query | %{"<Employee>"+$_.node.InnerXML+"</Employee>"}

Next the Select-Xml returns an XMLDocument object that is piped into a Foreach and wraps each set of child elements in its own Employee tag. We need this or we would just have the children in a string not grouped by Employee. You can think of it as wrapping each set of children of the node queried in an Employee tag.

Now we need a Root so we wrap $qryResult in a Reply tag and type it [xml]. Again if it were not well-formed the script would error out here.

$qryResultXML = [xml]("<Reply>"+$qryResult+"</Reply>")

Next we use dot notation and access the Employee Nodes of the XMLDocument object:

$qryResultXML.Reply.Employee

and pipe it into a Select-Object commandlet to grab the child items we need. I selected all in the script so I can delete each time what I don’t need. The resulting objects are then piped into the Export-Csv commandlet and saved locally.

Here’s the whole line:

$qryResultXML.Reply.Employee | 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

I then instantiate an Excel Com Object and open th csv file just created. I then activate the proper sheet and start formatting the first row and the columns so dates, phone numbers, dollar figures and other columns present well.

Next I autofit all columns used so I can see all the contents of the cells.

That’s it for now. Any comments or suggestions will be appreciated.