Apr

04

2017

Teknion_Blog_Header_Wherescape_Tip_-_PowerShell-1.png

WhereScape Tip: Using PowerShell to Translate XML SOAP Response to CSV

By: Raul Villaronga

The Wherescape RED automation tool works extremely well with main-stream sources of data, such as files and databases. However, in today’s world of cloud processing, there may be a need to pull data from the cloud through an API interface.

Here's a tip utilizing PowerShell 3.0 to take an XML SOAP Response document and write it to a CSV file so that Wherescape can process the data into a load table for ETL. One caveat is that the data needs to be single-level data, meaning that the individual record node cannot contain aggregate nodes within that need to be parsed.

First, build the Request XML and send it to the Web Service via the Invoke-WebRequest cmdlet, storing the Response XML into a variable. One example of this would be as follows:

[xml]$Response = Invoke-WebRequest -Uri $WSDLURI -Method Post -ContentType "text/xml" -Body $XMLRequest -Headers @{'Authorization'='None'}

Within the $Response XML object, all you need to do is identify the parent node where the iterative "records" reside and use the "ChildNodes" method on that node. In this example, the XML is formatted as follows (note that the individual record, i.e., “Entry,” has no aggregate nodes within it):

WhereScape Tip.png

In order to write the "Entry" data to a CSV, all you have to execute is the following single statement within PowerShell:

$Response.Envelope.Body.GetInfoResponse.GetInfoResult.data.diffgram.DocumentElement.ChildNodes | Export-Csv $fileName -Encoding ASCII -NoTypeInformation

Notice the navigation from the root node “Envelope” down to the “DocumentElement” node, where the multiple entries of data reside. We use that navigation from the XML to code the nodes in the $Response object. Using the PIPE (“|”) to concatenate the Export-Csv command passes the child nodes in to export to the CSV file.

This creates the comma-separated values file — COMMA delimited and QUOTES around each field. The file can then easily be imported into Wherescape just like any other file. That’s it!