Need Output file in JSON format

[DavidEllis1]

I am working on my first API Project.  It seems simple enough.  Another system needs a file from Archer but in JSON format.  I have created a report that has all the fields that the other system requires.  I am using PowerShell to script my APIs.  I used the sample code from the 2017 Charge  Powershell examples.  The only thing I did was change the $report_id to the report number that I created.

$api_url = $base_url + "/ws/search.asmx"
$ws = New-WebServiceProxy -Uri $api_url -Class Search -Namespace webservice
$report_id = 9091
$page = 1
Do {

"Page " + $page
[xml]$xdoc = $ws.SearchRecordsByReport($session_token, $report_id, $page)
$xdoc.InnerXml
$page++

} While ($xdoc.Records.Record.Count -gt 0)

I add and modified the other code samples to login and obtain a session token.  The API runs without any error and retrieves my report in XML format.  What do I need to modify in this code to export in the data in JSON format instead of XML?

Is this something that would be easier to do if I was using Visual Studio with C## instead of PowerShell?  I only used PowerShell since it was already installed and ready to go.

[jsol5]

Not familiar with Powershell...,

You asked about C#... 

Check out Scott Hagemeyer‌ 's code from 2017 Hello, World! API Basics Lab Documents from RSA Charge 2017 

I believe this code will show you how to get the response object and derserialize.

Hope this helps.

[JeffLetterman]

I found a function on the following page that may help: c# - Convert multiple XMLs to JSON list - Stack Overflow.  It isn't perfect, but it could be extended to meet your needs.

Here is the command to convert the XML into JSON.  I picked a high "depth" number to get everything.

$xdoc | ConvertFrom-Xml | ConvertTo-Json -Depth 10‍

# Helper function that converts a *simple* XML document to a nested hashtable with ordered keys.
function ConvertFrom-Xml {
  param([parameter(Mandatory, ValueFromPipeline)] [System.Xml.XmlNode] $node)
  process {
    if ($node.DocumentElement) { $node = $node.DocumentElement }
    $oht = [ordered] @{}
    $name = $node.Name
    if ($node.FirstChild -is [system.xml.xmltext]) {
      $oht.$name = $node.FirstChild.InnerText
    } else {
      $oht.$name = New-Object System.Collections.ArrayList 
      foreach ($child in $node.ChildNodes) {
        $null = $oht.$name.Add((ConvertFrom-Xml $child))
      }
    }
    $oht
  }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

XML

<?xml version="1.0" encoding="utf-16"?>
<Records count="1">
  <Metadata>
    <FieldDefinitions>
      <FieldDefinition id="129" guid="8cfba154-fc68-467c-810e-a9c6c69aa8a2" name="Facility Name" alias="Facility_Name" />
      <FieldDefinition id="152" guid="49df9dd6-5148-4226-8cc5-c41c684b5517" name="Type" alias="Type" />
      <FieldDefinition id="518" guid="2ed0a43d-3fa1-4090-85a0-3a6e06a0bdab" name="Description" alias="Description" />
      <FieldDefinition id="2846" guid="d831dc05-8275-4ae1-9b5e-45364835d04d" name="Phone" alias="Phone" />
      <FieldDefinition id="7859" guid="4df75847-63ae-4f52-8ee0-5e24d3a527af" name="Business Unit" alias="Business_Unit" />
      <FieldDefinition id="8099" guid="357fcfb3-6a88-48ca-9c01-3dced1c37c2b" name="Facility Manager" alias="Facility_Manager" />
    </FieldDefinitions>
  </Metadata>
  <LevelCounts>
    <LevelCount id="60" guid="7eb2544d-95aa-4122-9ee5-4f458f9cb1bc" count="1" />
  </LevelCounts>
  <Record contentId="226848" levelId="60" levelGuid="7eb2544d-95aa-4122-9ee5-4f458f9cb1bc" moduleId="69" parentId="0">
    <Field id="129" guid="8cfba154-fc68-467c-810e-a9c6c69aa8a2" type="1">Test Data Center</Field>
    <Field id="152" guid="49df9dd6-5148-4226-8cc5-c41c684b5517" type="4">
      <ListValues>
        <ListValue id="142" displayName="Data Center">Data Center</ListValue>
      </ListValues>
    </Field>
    <Field id="7859" guid="4df75847-63ae-4f52-8ee0-5e24d3a527af" type="9" />
    <Field id="518"  guid="2ed0a43d-3fa1-4090-85a0-3a6e06a0bdab" type="1">&lt;p&gt;My test center&lt;/p&gt;</Field>
    <Field id="8099" guid="357fcfb3-6a88-48ca-9c01-3dced1c37c2b" type="8">
      <Users>
        <User id="275" firstName="John" lastName="Doe">DoeJ</User>
      </Users>
      <Groups />
    </Field>
    <Field id="2846" guid="d831dc05-8275-4ae1-9b5e-45364835d04d" type="1" />
  </Record>
</Records>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

JSON

{
  "Records": [
    {
      "Metadata": [
        {
          "FieldDefinitions": [
            {
              "Facility Name": []
            },
            {
              "Type": []
            },
            {
              "Description": []
            },
            {
              "Phone": []
            },
            {
              "Business Unit": []
            },
            {
              "Facility Manager": []
            }
          ]
        }
      ]
    },
    {
      "LevelCounts": [
        {
          "LevelCount": []
        }
      ]
    },
    {
      "Record": [
        {
          "Field": "Test Data Center"
        },
        {
          "Field": [
            {
              "ListValues": [
                {
                  "ListValue": "Data Center"
                }
              ]
            }
          ]
        },
        {
          "Field": []
        },
        {
          "Field": "<p>My test center</p>"
        },
        {
          "Field": [
            {
              "Users": [
                {
                  "User": "DoeJ"
                }
              ]
            },
            {
              "Groups": []
            }
          ]
        },
        {
          "Field": []
        }
      ]
    }
  ]
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[DavidEllis1]

Thanks Jeff, I will give that a try. 

I assume that I will need to setup Visual Studio to take advantage of that?

[JeffLetterman]

The function is a PowerShell function...just copy/paste into your current PowerShell script.  Then add the convert command in the paging loop.

[DavidEllis1]

Jeff,

That seems to do the trick.  Now to test it out.  Thanks for the quick response.