XmlDataSourceProxy

A small YARP reverse proxy that makes JSON REST APIs consumable by the SSRS / Power BI Report Server XML data source. It forwards GET requests to a configured upstream and rewrites the JSON response as a flat, tabular XML document. Auth (API keys, bearer tokens, etc.) is configured declaratively using YARP's built-in transforms — no custom code.

Deployment model: this proxy is intended to run locally on the report server (the SSRS / PBIRS box) and listen on localhost only. SSRS data sources point at http://localhost:5080/.... The proxy injects API keys from its own config so the keys never appear in report definitions or shared data source connection strings. Do not expose it on a routable interface — it has no authentication of its own. By default it binds only to the loopback interface (127.0.0.1 and ::1).

Why

SSRS and Power BI Report Server ship an "XML" data source that can hit any URL and bind the response as a dataset — but it expects XML, not JSON, and its query language is finicky about element shapes and types. Most modern REST APIs return JSON. This proxy sits between them and emits a single predictable XML shape (<Root><Row>...</Row>...</Root>) so that:

• No SSRS dataset query is required (auto-detect finds <Row>).
• The same one-line query works for every endpoint.
• A ?_query=1 debug URL returns a paste-ready <Query> document with inferred field types (Integer, Decimal, Boolean, DateTime).

How it works

1. A YARP route maps an incoming proxy path to an upstream cluster (appsettings.json → ReverseProxy:Routes / Clusters).
2. Auth and any other request shaping uses YARP's built-in transforms (RequestHeader, QueryValueParameter, PathPattern, …).
3. A response transform buffers the upstream JSON and emits a tabular XML document — always the same shape, so the SSRS dataset needs no query at all (auto-detect picks <Row>), or one boilerplate query that works for every endpoint:

   <Query><ElementPath IgnoreNamespaces="true">Root/Row</ElementPath></Query>

4. Appending ?_query=1 to any proxied URL returns a generated <Query> document with type-inferred fields, ready to paste into Report Builder.

Output shape

<Root>
  <Row>...scalar fields...</Row>
  <Row>...</Row>
</Root>

Row selection

1. If the route has Metadata.RowPath configured, that dotted JSON path is resolved; if it's an array, its items are the rows.
2. Else if the top-level JSON value is an array, its items are the rows.
3. Else if the top-level value is an object with exactly one record-like array property (array of objects), that array's items are the rows.
4. Else the whole top-level object is a single row.
5. A scalar top-level value becomes one row with a <Value> field.

Row flattening

• Nested objects collapse with _: address.geo.lat → <address_geo_lat>.
• Nested arrays are serialized as a compact JSON string in one element (so the row stays tabular and the column set is fixed per endpoint).
• null → empty element. Numbers/booleans → text form.
• XML-illegal characters in JSON property names are sanitized (@context → _context, leading xml* is prefixed with _).

Configuring an endpoint

Add a route + cluster under ReverseProxy. Authentication is just another transform — no custom config section needed.

"ReverseProxy": {
  "Routes": {
    "myapi": {
      "ClusterId": "myapi",
      "Match": { "Path": "/myapi/{**catch-all}" },
      "Metadata": { "RowPath": "data.items" },   // optional, only if rows are nested
      "Transforms": [
        { "PathPattern": "/{**catch-all}" },
        { "RequestHeader": "X-API-Key", "Set": "REPLACE-ME" }
      ]
    }
  },
  "Clusters": {
    "myapi": {
      "Destinations": {
        "d1": { "Address": "https://api.example.com/" }
      }
    }
  }
}

Auth recipes (all standard YARP transforms)

Goal	Transform
API key in a header	{ "RequestHeader": "X-API-Key", "Set": "KEY" }
Bearer token	{ "RequestHeader": "Authorization", "Set": "Bearer KEY" }
API key in a query parameter	{ "QueryValueParameter": "api_key", "Set": "KEY" }
Basic auth	{ "RequestHeader": "Authorization", "Set": "Basic <base64(user:pass)>"}
Custom User-Agent (e.g. NWS)	{ "RequestHeader": "User-Agent", "Set": "MyApp (you@example.com)" }

Keep secrets out of appsettings.json

Layered .NET config means env vars or User Secrets override the file. The Transforms array is positional; the key is the transform's index:

# Override the "Set" value of the 2nd transform on route "myapi"
$env:ReverseProxy__Routes__myapi__Transforms__1__Set = "real-secret-key"

Or in dev:

dotnet user-secrets set "ReverseProxy:Routes:myapi:Transforms:1:Set" "real-secret-key"

Bundled sample routes

Run the proxy and try:

Proxy URL	Upstream
/jsonplaceholder/posts/1	https://jsonplaceholder.typicode.com/posts/1
/jsonplaceholder/users	https://jsonplaceholder.typicode.com/users
/openlibrary/search.json?q=tolkien	https://openlibrary.org/search.json?q=tolkien
/weather/points/39.7456,-97.0892	https://api.weather.gov/points/39.7456,-97.0892
/echo/headers (demo: API key in header)	https://httpbin.org/headers
/echo/query (demo: API key in query)	https://httpbin.org/get

Append ?_query=1 to any of these to get a paste-ready <Query> document.

Run it (dev)

dotnet run

Listens on http://localhost:5080 by default (loopback only). Override with --urls or the ASPNETCORE_URLS environment variable if you really must bind elsewhere — but see the warning at the top.

Install as a Windows service (production)

The app uses Microsoft.Extensions.Hosting.WindowsServices, so it can be launched directly by the Service Control Manager.

Recommended: use the bundled scripts

From an elevated PowerShell prompt in the cloned repo:

.\install.ps1

That will:

1. dotnet publish a self-contained win-x64 build into C:\Program Files\XmlDataSourceProxy (override with -InstallDir).
2. Create a service named XmlDataSourceProxy running under the NT SERVICE\XmlDataSourceProxy virtual account (auto-created by SCM, no password to manage).
3. Grant that account read/execute on the install directory.
4. Configure auto-start and a restart-on-failure policy.
5. Start the service and verify it's listening on 127.0.0.1:5080.

Re-running install.ps1 performs an in-place upgrade (stop, overwrite, restart).

To remove:

.\uninstall.ps1            # stop + delete service, leave files
.\uninstall.ps1 -RemoveFiles   # also delete C:\Program Files\XmlDataSourceProxy

Manual install (if you'd rather not use the scripts)

dotnet publish -c Release -r win-x64 --self-contained `
    -o "C:\Program Files\XmlDataSourceProxy"

sc.exe create XmlDataSourceProxy `
    binPath= "\"C:\Program Files\XmlDataSourceProxy\XmlDataSourceProxy.exe\"" `
    start= auto `
    obj= "NT SERVICE\XmlDataSourceProxy" `
    DisplayName= "XML Data Source Proxy for SSRS/PBIRS"

sc.exe start XmlDataSourceProxy

Logs go to the Windows Application event log under source XmlDataSourceProxy by default. For richer/structured logs, drop a Serilog/Seq sink into Program.cs — not included to keep the project minimal.

Where to put appsettings.json in production

The published folder (C:\Program Files\XmlDataSourceProxy) contains the default appsettings.json from this repo. Either edit it in place or override individual settings with environment variables (preferred for secrets — env vars don't get checked into source control, and the virtual service account can read them without anyone seeing them in JSON):

# Per-service environment variable (PowerShell, elevated).
New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\XmlDataSourceProxy" `
    -Name Environment -PropertyType MultiString `
    -Value @("ReverseProxy__Routes__myapi__Transforms__1__Set=real-secret-key")
Restart-Service XmlDataSourceProxy

Using from SSRS / PBIRS

1. Data source type: XML. Connection string: the proxy URL.
2. Create a dataset. Either leave the query empty (auto-detect finds <Row>) or paste the boilerplate <Query><ElementPath IgnoreNamespaces="true">Root/Row</ElementPath></Query>.
3. For real types instead of String, hit the URL with ?_query=1, copy the returned <Query>...</Query> block, and paste that into the dataset Query box. It includes inferred Integer / Decimal / Boolean / DateTime annotations.
4. Refresh Fields, drag, render.

Project layout

Program.cs                       -- wires YARP + middleware
DesignTimeQueryMiddleware.cs     -- snoops/strips ?_query=1
JsonToXmlTransformProvider.cs    -- registers the response transform per route
JsonToXmlResponseTransform.cs    -- JSON -> tabular XML
QueryDocumentBuilder.cs          -- builds the <Query> document
appsettings.json                 -- routes, clusters, transforms