Important Update: Some Community URL Redirects are Under Maintenance. Learn More. .

cancel
Showing results for 
Search instead for 
Did you mean: 

Need Output file in JSON format

DavidEllis1
Collaborator III

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.

5 REPLIES 5

jsol5
Advocate II

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
Archer Employee
Archer Employee

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": []
        }
      ]
    }
  ]
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thanks Jeff, I will give that a try. 

 

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

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

Jeff,

 

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