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

I'm working with the 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 : /developerbook

  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'