XML data delivery, on purpose

🧪

Hands-on lab

Deploy one RefData package's plain JSON seed data to the SQL Server 2008-floor tier and watch it land, converted to XML as it is read — under the warn policy and under fail alike, with no downgrade recorded. Deploy the same JSON package to the compat-160 tier, then the XML-declared twin to both. Extract the XML payload with datatongs --DeliveryEncoding:Xml, see that an unswitched extract from the compat-100 database still writes JSON, and redeploy the round trip. Finally deploy the XML delivery to PostgreSQL, MySQL and MariaDB, and meet the MySQL-family refusal for tokenized XML merge scripts.

Demos/Learn/course10-xml-data-delivery →

Module 4 watched SchemaSmith read its own model as XML on the compat-100 tier. Point a plain JSON seed file at that same tier and it bends for your data too. So why does ContentEncoding: "Xml" still exist?

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith.

Module 4 took you to the bottom of the farm and showed the wire format bend by itself — the model ingest reading its payload as XML below compat 130, no knob touched, and one log line to tell you it did. This recipe is the coda, on the same tier, for the seed data you ship alongside your schema. It crosses the same cliff the same way now, which leaves one honest question: when is declaring XML yourself still the right call?

Beat one — JSON lands on the floor

Deploy the JSON package to learn_2008, a SQL Server database sitting at compatibility level 100, the laggard from Module 4. No ContentEncoding on the delivery, so it’s JSON:

"DataDelivery": {
  "ContentFile": "data/dbo.CountryCode.json.tabledata",
  "MergeType": "Insert/Update",
  "MatchColumns": "Code"
}

[localhost,11433].[learn_2008]   [learn_2008] detected SQL Server version 16.0.4260.1 (compatibility level 100)
[localhost,11433].[learn_2008]   [learn_2008] model ingest encoding: Xml (auto)
[localhost,11433].[learn_2008]   Delivering table data
[localhost,11433].[learn_2008]     Delivering dbo.CountryCode (converted to Xml for compatibility level 100)
[localhost,11433].[learn_2008] Successfully Quenched

Exit code 0. Read the second line, then the fourth. The model ingest switched to XML for this database, and so did your delivery: the JSON file was converted to the XML row shape as it was read and went through the XML merge. Read the table back:

Code  Name            Continent
----  --------------  -------------
CA    Canada          North America
DE    Germany         Europe
GB    United Kingdom  Europe
JP    Japan           NULL
US    United States   North America

Five rows, on a tier where OPENJSON can’t even parse. JP’s JSON null arrived as NULL.

Beat two — the policy has nothing to say

Set Target:UnsupportedFeaturePolicy to fail and deploy the same JSON package again. Same lines, exit 0, and the deployment summary’s unsupportedDowngrade is null. That’s not the policy being lenient. A downgrade is SchemaSmith dropping part of what you declared because the target can’t hold it; a conversion drops nothing. Decimals, dates, binary, spatial values with their SRID — they land the same through the conversion as through OPENJSON. There’s nothing to warn about and nothing to refuse.

Beat three — one JSON package, every tier

Deploy the same package to learn_2022, the compat-160 tier:

[localhost,11433].[learn_2022]   Delivering table data
[localhost,11433].[learn_2022]     Delivering dbo.CountryCode
[localhost,11433].[learn_2022] Successfully Quenched

No conversion note, because this target reads JSON directly. Same five rows. One JSON package covers the whole SQL Server fleet; you don’t keep an XML copy for the laggard.

The rule now

ContentEncoding describes the file. It’s never inferred and never rewritten. Below compat 130, a Json file is converted as it’s read; an Xml file is shipped as written, on every tier and every engine.

So declaring Xml no longer buys reach. It’s the right call when the file is XML:

• DataTongs wrote it from a SQL Server source older than 2017 — that’s its default there, as Beat five shows.
• Someone handed you a file in that shape, or a consumer downstream of the file wants XML.

If you’re producing the file yourself from anything newer, ship JSON. It reaches every tier now, and it’s far easier to read in a diff.

Beat four — the XML contract

Here’s the same delivery declared as XML:

"DataDelivery": {
  "ContentFile": "data/dbo.CountryCode.xml.tabledata",
  "ContentEncoding": "Xml",
  "MergeType": "Insert/Update",
  "MatchColumns": "Code"
}

The XML content file carries the same five rows in a fixed shape — a rows root, one row per record, one c per column with an n attribute naming it:

<rows>
<row><c n="Code">US</c><c n="Name">United States</c><c n="Continent">North America</c></row>
<row><c n="Code">CA</c><c n="Name">Canada</c><c n="Continent">North America</c></row>
<row><c n="Code">GB</c><c n="Name">United Kingdom</c><c n="Continent">Europe</c></row>
<row><c n="Code">DE</c><c n="Name">Germany</c><c n="Continent">Europe</c></row>
<row><c n="Code">JP</c><c n="Name">Japan</c></row>
</rows>

Look at the JP row. It’s missing a c for Continent entirely — not an empty tag, not a null marker, just absent. That absence is the NULL. It’s also exactly what Beat one’s conversion did with JP’s JSON null: a null value becomes a missing element.

Deploy it to learn_2008 and to learn_2022:

[localhost,11433].[learn_2008]   Delivering table data
[localhost,11433].[learn_2008]     Delivering dbo.CountryCode
[localhost,11433].[learn_2008] Successfully Quenched

Exit code 0 on both, no conversion note — the file is already XML. Same five rows, JP.Continent NULL on each tier.

Beat five — where an XML file comes from

Five country codes are easy to type. Two hundred rows and fifteen columns are not, and a recipe that stopped at “here’s the shape” would have handed you a technique you can’t use. DataTongs writes it:

datatongs --ConfigFile:tongs.settings.json --DeliveryEncoding:Xml

<rows><row><c n="Code">CA</c><c n="Continent">North America</c><c n="Name">Canada</c></row>…<row><c n="Code">JP</c><c n="Name">Japan</c></row>…

Find JP again. A Code, a Name, and no Continent element — the convention emitted without being asked. NULL-as-absent-element isn’t a rule you have to remember when hand-authoring. It’s what the extractor produces, because it’s how the shred reads the file at the other end.

Leave the switch off and DataTongs chooses by the source server’s version. Its JSON extraction needs FOR JSON and STRING_AGG, which arrived in SQL Server 2017, so against an older source it extracts XML and tells you:

  Delivery data is extracted as Xml: the source is SQL Server <version>, and the Json extraction needs SQL Server 2017. Set DeliveryEncoding to choose.

Force Json against that source and the run is refused, exit 2:

FAILED: DeliveryEncoding is Json, but the source is SQL Server <version>, and the Json extraction needs SQL Server 2017 or later. Set DeliveryEncoding to Xml, or leave it unset to choose automatically.

That’s the reason you’ll meet XML files in the wild: anyone extracting reference data from a 2008 R2–2016 production box gets them by default. Declare Xml on those deliveries, because that’s what the files are.

The lab can’t show those two lines — every tier in the sandbox is one SQL Server 2022 instance. What it shows is the twist: the choice follows the server’s version, not the database’s compatibility level, so with no switch an extract from the compat-100 learn_2008 picks JSON — and that JSON lands right back on the compat-100 tier, as the first section showed. Want XML from it anyway? That’s where you pass --DeliveryEncoding:Xml.

The loop closes either way. The XML file DataTongs writes is a drop-in for the one you were handed: drop it into the package, redeploy to the compat-100 tier, same five rows, JP still NULL. Extract as XML, deploy as XML, no hand-editing anywhere in it.

The other engines

PostgreSQL, MySQL and MariaDB have no cliff — each shreds JSON at every version SchemaSmith supports. They accept an XML file too, so a file extracted from an old SQL Server deploys there unchanged:

[localhost].[learn]         Create new table public.countrycode
[localhost].[learn]         Add missing Constraint public.countrycode.pk_countrycode
[localhost].[learn]   Delivering table data
[localhost].[learn]     Delivering public.countrycode
[localhost].[learn] Successfully Quenched

Exit code 0, five rows, JP’s continent still NULL. PostgreSQL shreds the payload natively with xmltable(). MySQL and MariaDB reject dynamic XPath, so SchemaSmith converts the payload to JSON once up front and runs it through the ordinary JSON row source.

Declaring Xml buys nothing on these engines, and on MySQL and MariaDB it can cost you. DataTongs can write merge scripts beside the content file, and by default those scripts are tokenized: they read the content file with JSON_TABLE when they’re deployed. Nothing converts an XML file on that path, so DataTongs refuses the combination:

FAILED: DeliveryEncoding is Xml, but MySQL and MariaDB merge scripts read their content file with JSON_TABLE, so tokenized scripts cannot deploy an Xml file. Use DeliveryEncoding Json, or set ShouldCast:TokenizeScripts to false to embed the data in each script.

Exit code 2, before anything’s extracted. No content file, no scripts, nothing half-written to tidy up. A DataDelivery is unaffected — it converts the file first.

Two things before you lean on it

Column order isn’t stable, and doesn’t need to be. SQL Server and PostgreSQL emit columns alphabetically; MySQL and MariaDB emit them in table order. Diff two extracts from different engines and the columns move. It doesn’t matter — every value is addressed by its n attribute, never by position. Just don’t build anything that assumes an order.

One real gap: spatial columns out of PostgreSQL and MySQL. A geometry or geography column normally extracts as WKT plus a companion <c n="Column.STSrid"> carrying its spatial reference system, and the SQL Server shred needs that companion to rebuild the value exactly. Neither engine’s extraction captures the SRID today — and this is not an XML problem: a JSON payload from those engines loses it identically, XML just inherited the gap.

It usually self-heals, which is why you may never have noticed it. A typed column — geometry(Point,4326), or a MySQL column restricted with an SRID attribute — coerces whatever arrives to its declared SRID, so the round trip is lossless. The case that bites is an untyped spatial column holding a non-default SRID: it comes back as SRID 0 with the coordinates intact and no error at all. Right numbers, wrong reference system, silently — which for geospatial is worse than a failure. Extract spatial data from SQL Server, type the destination column, or set the SRID yourself. Everything else — binary, dates, booleans, NULLs — is fully portable.

What just happened

Module 4 showed the engine bending the wire format for its model. This recipe showed it bending for your data too: a JSON delivery aimed below compat 130 is converted as it’s read, lands every row under warn and fail alike, and records no downgrade. One JSON package covers every tier.

That changes what ContentEncoding: "Xml" means. It’s never inferred and never rewritten, and it now says one thing: this file is XML. You’ll declare it for files DataTongs wrote from a SQL Server older than 2017, where XML is the default and Json is refused, and for files someone hands you in that shape. Anywhere else, ship JSON.

And you never have to hand-write the payload. datatongs --DeliveryEncoding:Xml produces it on any source engine, NULLs and all — just not with tokenized merge scripts on MySQL or MariaDB.

One package, the oldest tier included — and a round trip that closes.

Subscribe and stick around — there’s more coming from the forge.

Until then, may every row you ship travel in a tongue its tier can actually read.

— Forge

Check yourself: Your compat-100 tenant takes the plain JSON seed package and all five rows land. A teammate says every delivery bound for that tenant must declare ContentEncoding: Xml anyway. When are they right?

Only when the file actually is XML. From 2.8 a JSON delivery aimed at a SQL Server database below compatibility level 130 is converted to the XML row shape as it is read and goes through the XML merge — nothing is skipped and no downgrade is recorded, under warn and fail alike. ContentEncoding describes the file and is never rewritten, so declaring Xml on a JSON file would be wrong. Declare it for a file DataTongs extracted as XML (its default against a SQL Server source older than 2017) or one handed to you in that shape.

Check yourself: DataTongs against a SQL Server 2014 source with no DeliveryEncoding writes XML files. A colleague sets DeliveryEncoding to Json and the run stops with exit 2. Why both — and what does the same unswitched extract pick from your compat-100 database on a SQL Server 2022 server?

The JSON extraction is built on FOR JSON and STRING_AGG, and STRING_AGG arrived in SQL Server 2017. So with DeliveryEncoding unset, DataTongs picks XML for a source server older than 2017 and logs that it did; an explicit value always wins, and an explicit Json it cannot run is refused with exit 2 rather than failing halfway. The choice keys on the source server’s version, not on a database’s compatibility level, so the compat-100 database on a 2022 server gets JSON. Pass —DeliveryEncoding:Xml there: a JSON extract from a database at level 100 currently fails (#573).

Check yourself: You extract a reference table with datatongs --DeliveryEncoding:Xml from your PostgreSQL source, then diff the file against the XML payload a teammate extracted from SQL Server. The columns appear in a different order, and one nullable column is missing from some rows entirely. Which of these is a problem?

Neither one. Both are the format working as designed.

The column ORDER differs because SQL Server and PostgreSQL emit columns alphabetically while MySQL and MariaDB emit them in table order. It has no effect: every value is addressed by its n attribute, never by position, so the shred finds n="Continent" wherever it sits in the row. Just don’t write anything that assumes an order.

The MISSING element is the NULL contract. An absent c for a column means NULL for that row — not an empty string, not a parse error. You saw it hand-written earlier and the extractor reproduces it without being asked, because that is how the shred reads the file at the other end.

The thing that WOULD be a problem in that diff is a spatial column. A geometry or geography value normally carries a companion <c n="Column.STSrid">, and PostgreSQL’s and MySQL’s extraction does not capture the SRID today — so the WKT arrives without the reference system the SQL Server shred needs. A typed destination column (or a MySQL column with an SRID attribute) puts it back; an untyped one silently lands SRID 0. Extract spatial data from SQL Server, or set the SRID on the destination yourself.

Mark this lesson complete