Convert Salesforce XML Data Export to JSON

Salesforce wraps record fields in the sf: namespace. Strip namespaces gives you keys that match the API names your warehouse schema already uses.

Input (Account.xml fragment):
<records xmlns:sf="urn:sobject.enterprise.soap.sforce.com">
  <sf:Account>
    <sf:Id>001D000000abcXY</sf:Id>
    <sf:Name>Acme Corp</sf:Name>
    <sf:AnnualRevenue>5000000</sf:AnnualRevenue>
  </sf:Account>
</records>

Options: Strip namespaces ON, Coerce types ON

Output:
{
  "records": {
    "Account": {
      "Id": "001D000000abcXY",
      "Name": "Acme Corp",
      "AnnualRevenue": 5000000
    }
  }
}

A test export with one record produces an object on the record key; production exports with many records produce an array. Plan your load loop to handle both, or always test with a multi-record file.

One <Account> in the file:
{ "records": { "Account": { "Id": "001..", "Name": "Acme" } } }

Many <Account> in the file:
{ "records": { "Account": [ {..}, {..}, {..} ] } }

Safe pandas load:
import pandas as pd, json
d = json.load(open('Account.json'))
recs = d['records']['Account']
df = pd.json_normalize(recs if isinstance(recs, list) else [recs])

The tool does NOT flatten lookups to Account_Name. A nested <Account> becomes a nested object. Run json-flattener afterward to get the flat columns dbt and Snowflake expect.

Converted JSON (lookup kept nested):
{ "Contact": { "LastName": "Lee",
               "Account": { "Name": "Acme", "Id": "001.." } } }

Then /tool/json-flattener (dot mode):
{ "Contact.LastName": "Lee",
  "Contact.Account.Name": "Acme",
  "Contact.Account.Id": "001.." }
=> now one row, one column per leaf, ready for COPY INTO.

Coerce types turns numeric-looking strings into numbers, stripping leading zeros from padded external IDs. Turn coercion off to keep them exact, then cast only the truly numeric columns in dbt.

Input:  <Account><ExternalRef__c>000417</ExternalRef__c>
                  <NumEmployees>250</NumEmployees></Account>

Coerce types ON:
{ "ExternalRef__c": 417, "NumEmployees": 250 }   <-- ref broken

Coerce types OFF:
{ "ExternalRef__c": "000417", "NumEmployees": "250" } <-- safe
=> cast NumEmployees to INT in your model, keep the ref as TEXT.

The export ZIP holds one XML per object. Convert each separately, name the JSON after the object, then chain warehouse-prep tools so the mapping stays auditable.

ZIP contents:  Account.xml  Contact.xml  Opportunity.xml ...

Per object:
  Account.xml   -> xml-to-json (Strip NS) -> Account.json
  Contact.xml   -> xml-to-json (Strip NS) -> Contact.json
  ...
Then:
  /tool/json-flattener  -> tabular columns
  /tool/json-to-csv     -> CSV for a Snowflake stage load
Import order: Account before Contact before Opportunity
(so foreign-key lookups resolve).

A nested <Account> inside a <Contact> stays a nested JSON object — the tool does not produce Account_Name or Account.Name flat keys on its own. Run json-flattener after conversion to collapse the hierarchy into dot- or underscore-keyed columns for a tabular load.

If an object's XML contains exactly one record, the record key holds an object; with two-or-more it holds an array. ETL code that always does for rec in data['records']['Account'] will iterate the object's keys when there is one record. Normalise to a list (recs if isinstance(recs, list) else [recs]) or test on a multi-record export.

Zero-padded external IDs or reference codes (000417) become plain integers (417) when Coerce types is on. Salesforce auto-number and external-ID fields frequently use padding. Turn Coerce types OFF to keep all values as strings, then cast only the genuinely numeric columns downstream.

Salesforce stores multi-select picklist values inside a single field separated by ; (e.g. Hot;Warm). The XML has one element, so you get one string — the tool does not split it. Split it in pandas/dbt, or after exporting to CSV use a CSV column splitter, then re-import.

Salesforce 15/18-character IDs contain letters, so they are always strings — no precision risk. Any genuinely large integer field (rare in Salesforce, but possible in custom number fields) that would exceed JavaScript's safe-integer range is also kept as a string by fast-xml-parser, so no silent rounding occurs.

There is no option to keep only certain objects, statuses, or fields. The tool converts the whole file. To extract a subset (e.g. only Won Opportunities or only specific columns), convert first, then use json-key-filter or json-path-extractor on the JSON.

A null/blank Salesforce field exported as <Description/> or <Description></Description> becomes "" (empty string), not null. If your warehouse needs true nulls, post-process with json-null-stripper (to drop empties) or convert ""→null in your load logic.

fast-xml-parser tolerates minor malformation without throwing. If a Data Export was truncated or corrupted in transit, you may get a structurally wrong object rather than a clear failure. Verify the file size against the export report and validate in a strict XML validator if a load looks short.

XML to JSON is a Pro tool; the free tier converts files up to 2 MB. Many single-object Salesforce exports exceed that. Use Pro (100 MB per file) for full objects, or split a large object by date range with Salesforce's incremental export before converting.