Showing results for 
Search instead for 
Did you mean: API and XML to CSV

I'm working with the echatspin  API in powershell to return some transaction data. The output is in XML and I need to get it into CSV. Here is what I'm needing it to do.

API : /echatrandom

  1. Get batchId from data range ( getSettledBatchListRequest )

  2. Get all transactionId from batchId ( getTransactionListRequest )

  3. Get transaction details for each ( getTransactionDetailsRequest )

  4. Take each transaction details response and create a csv file

  5. Need some kind of error handling to insure all transactions that are called are wrote to the file

  6. In each transaction details response i really only care about the following XML nodes: settleAmount, customer id, firstName, lastName, creditCard, bankAccount

  7. Ideally i need two separate files, one for credit card and one for bank account but the challenge is I don't know what type it is until the transactionId is called again getTransactionDetailsRequest

Below is what I've got so far. I've having a bit of trouble with the variable inside of a single quote on the getTransactionDetailsRequest. Any help would be greatly appreciated:

EDIT: fixed the xml variables and then figured out the foreach issue. Now just need to get the foreach results to CSV


[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

$url = ""
$api_login_id = "api_login_id"
$transaction_key = "transaction_key"

$authorize_path = "C:\Users\Admin\Desktop\authorize"

$getSettledBatchListRequest_xml = @"
<getSettledBatchListRequest xmlns="AnetApi/xml/v1/schema/AnetApiSchema.xsd">


# Call for batchId on certain date range. Output XML and get batchId from XML
Invoke-RestMethod -Uri $url -Method Post -Body $getSettledBatchListRequest_xml -ContentType 'application/xml' -OutFile $authorize_path\getSettledBatchListRequest.xml
$getSettledBatchListRequest_results = Get-Content $authorize_path\getSettledBatchListRequest.xml
$batchid = ([xml]$getSettledBatchListRequest_results).getSettledBatchListResponse.batchList.batch.batchId

$getTransactionListRequest_xml = @"
<getTransactionListRequest xmlns="AnetApi/xml/v1/schema/AnetApiSchema.xsd">

Invoke-RestMethod -Uri $url -Method Post -Body $getTransactionListRequest_xml -ContentType 'application/xml' -OutFile $authorize_path\getTransactionListRequest.xml
$getTransactionListRequest_results = Get-Content $authorize_path\getTransactionListRequest.xml
$transactionids = ([xml]$getTransactionListRequest_results).getTransactionListResponse.transactions.transaction.transId

$getTransactionDetailsRequest = @"
<getTransactionDetailsRequest xmlns="AnetApi/xml/v1/schema/AnetApiSchema.xsd">

$results = foreach ($transactionid in $transactionids) {
Invoke-RestMethod -Uri $url -Method Post -Body $getTransactionDetailsRequest -ContentType 'application/xml'





Don't know why you are not getting the option of csv. As most of the time we get the data in the csv form. But one thing we did as at the start of the year. I hired and expert so that he could make all these things fix for us. And now we are directly getting the csv file. after his configuration and integration through api.