XML data delivery for the oldest tier

🧪

Hands-on lab

Deploy one RefData package's seed data to the SQL Server 2008-floor tier three ways: a JSON DataDelivery that gets skipped under the warn policy, then made to abort under a fail policy, then re-encoded as XML to land all five rows — JP's Continent NULL from the absent c element. Deploy the identical XML package to the compat-160 tier next, one encoding for every floor. Then deploy the same XML delivery to PostgreSQL, where it lands via xmltable() — the encoding is accepted on every engine from 2.5.0, but only SQL Server needs it. Finally close the round trip: extract the payload with datatongs --DeliveryEncoding:Xml and redeploy it unchanged.

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

You watched SchemaSmith’s own model ingest switch encodings on its own, beneath your notice. Point a plain JSON DataDelivery at that same legacy tier and nothing bends for you — the rows just don’t land, and the deploy tells you it’s your move.

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 engine’s model ingest reading its own payload as XML below compat 130, no knob touched, nothing in the log to make you care. This recipe is the coda, on the same tier, for the one surface that doesn’t get that courtesy: the seed data you ship alongside your schema. Let’s fire it up and watch the rows land — or not.

The one surface that won’t bend for you

Here’s the rule this whole recipe turns on: ContentEncoding on a DataDelivery is never inferred. Leave it blank and it’s JSON — always, on every engine, on every tier, no matter what compatibility level the target reports. SchemaSmith will happily read your table’s own model as XML on a compat-100 database without asking. It will not do the same favor for your data. That choice is yours, made explicit, every time.

Point a blank-encoding — JSON — delivery at a database below compat 130 and one of two things happens, controlled by Target:UnsupportedFeaturePolicy:

• warn (the default) — the delivery is skipped. The table still gets created. The run still exits clean. Your rows are just not there.
• fail — the run aborts before it finishes, non-zero exit, nothing else deploys either.

Either way, the engine tells you exactly what to do about it. It just won’t do it for you.

The block, both ways

Same table, same package, one property. Here’s the delivery that ships JSON — the default, no encoding set:

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

And here’s that same delivery re-encoded for the legacy tier — one property added:

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

The XML content file carries the same five rows as the JSON one, 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. Leave a column’s c out of a row and the delivery lands that column as NULL for that row. Hold onto that; it’s the detail that proves the readback later.

Beat one — the JSON delivery gets skipped

Deploy the JSON-encoded package to learn_2008, a SQL Server database sitting at compatibility level 100 — the same laggard from Module 4. The default policy is warn:

[localhost,11433].[learn_2008]   detected SQL Server version 16.0.4260.1 (compatibility level 100)
[localhost,11433].[learn_2008]   Delivering table data
[localhost,11433].[learn_2008]     [SKIPPED - requires compatibility level 130 for JSON delivery] JSON data delivery for dbo.CountryCode requires SQL Server compatibility level 130 (target is at 100); re-encode this delivery as XML ("ContentEncoding": "Xml") to deploy it on a legacy-compat target.
[localhost,11433].[learn_2008] Successfully Quenched

Exit code 0. The run reports success because a skip isn’t a failure under warn — the table gets created, the constraints land, and the delivery step just quietly doesn’t happen. Read the table back and it’s there, empty: SELECT COUNT(*) FROM dbo.CountryCode returns 0.

Nothing crashed. Nothing turned red. Your seed data is just gone, and unless you’re reading logs line by line, that’s easy to miss.

Beat two — the fail policy won’t let it stay quiet

Set Target:UnsupportedFeaturePolicy to fail on the same JSON package and deploy again:

[localhost,11433].[learn_2008] FAILED to quench:
JSON data delivery for dbo.CountryCode requires SQL Server compatibility level 130 (target is at 100); re-encode this delivery as XML ("ContentEncoding": "Xml") to deploy it on a legacy-compat target.
[localhost,11433].[learn_2008] *** FAILED [Template:Main] ***
Template 'Main' had 1 failed work unit(s)

Exit code 2. Same message, same diagnosis, but now it stops the run instead of shrugging past it. fail is the policy for a pipeline where a silently-empty table is worse than a red build. Either way you land on the identical instruction: re-encode as XML.

Beat three — the money shot: XML lands every row

Take that instruction. Swap ContentFile to the XML payload, add "ContentEncoding": "Xml", and deploy the same package to the same learn_2008:

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

Exit code 0 — no skip line this time, because there’s nothing to skip. 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 the one tier OPENJSON can’t even parse. JP.Continent reads NULL — the absent c element from the payload doing exactly what it promised. This is the whole recipe in one readback: the same seed data that a compat-100 target silently refused under JSON lands clean the moment you tell it to travel as XML.

Beat four — one encoding, every tier

Deploy that same XML package — unchanged, same ContentFile, same ContentEncoding — to learn_2022, the modern compat-160 tier from Module 4:

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

Same five rows, JP.Continent still NULL. XML shreds at every compat level, the same way .nodes()/.value() did back in Module 4 — so once you’ve re-encoded for the floor, you haven’t given anything up on the ceiling. You don’t need a JSON package for the modern tenants and an XML package for the laggard. One encoding covers the whole fleet.

The other engines take it too — for a different reason

One scope note, and it’s the honest kind.

The other three engines have no cliff. PostgreSQL, MySQL, and MariaDB shred JSON at every version they support, because none of them built their JSON parsing on a feature that arrived decades after the floor version SchemaSmith still reaches. Nothing to fall off, nothing to re-encode around.

They still accept the encoding. Declare ContentEncoding as XML on PostgreSQL and it lands:

[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 outright, so SchemaSmith converts the payload to JSON once up front and runs it through the ordinary JSON row source — same rows, and the difference never shows up in your package.

So hold two facts at once, because they pull in opposite directions. On SQL Server, Xml buys version reach — below compat 130 it is the only wire format that works at all. On the other three it buys nothing on its own, because JSON already works everywhere. What it buys there is uniformity: one DataDelivery block and one payload file that a shared package can point every engine at, instead of an XML declaration for the SQL Server laggard and a JSON one for its siblings.

That’s the whole reason to reach for it outside SQL Server. If nothing in your fleet sits below compat 130, omit ContentEncoding entirely and ship JSON — it’s the default, and it’s far easier to read in a diff.

Needs 2.5.0. Before then this section taught the opposite: ContentEncoding: "Xml" was rejected outright on PostgreSQL, MySQL, and MariaDB. On 2.4.0 or earlier you’ll still get that rejection.

Where that payload actually comes from

Go back to the XML content file you were shown earlier. Five country codes, one c element per column, and JP missing its Continent element entirely because absent means NULL. Easy enough to type.

Now make it a real reference table. Two hundred rows, fifteen columns, three of them nullable. You are not typing that, and if this recipe stopped at “here’s the shape”, it would have handed you a technique you can’t use.

You don’t type it. DataTongs writes it — the same tool that casts your reference data into a content file in the first place, just told which encoding you want:

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 you were told to follow, emitted without being asked. That’s worth sitting with for a second, because it reframes the rule: NULL-as-absent-element isn’t a convention 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. The hand-written payload was written to match the tool, not the other way round.

--DeliveryEncoding takes Json (the default) or Xml, and both work on every source engine. SQL Server builds the XML natively; PostgreSQL, MySQL and MariaDB extract their normal JSON and convert it to this identical dialect. The file doesn’t betray which engine produced it.

And the loop closes. The 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.

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) — 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 you the one place SchemaSmith bends the wire format for you. This recipe showed you the one place you have to bend it yourself. ContentEncoding is never inferred — blank is JSON, always — and a JSON delivery aimed below compat 130 gets skipped under the warn default or aborts the run under fail. Either way, the fix is the same one line: set ContentEncoding to Xml by hand. Do that and the identical package lands rows on the compat-100 floor and the compat-160 ceiling alike, JP’s NULL riding the absent c element the whole way.

And the same knob works on PostgreSQL, MySQL, and MariaDB — but ask yourself why you’re reaching for it there. On SQL Server it buys version reach. On the other three it buys one shared declaration instead of two. Those are different purchases, and only one of them is about old servers.

And you never have to hand-write the payload. datatongs --DeliveryEncoding:Xml produces it on any source engine, NULLs and all, which is what turns this from a demo into something you can point at a real reference table.

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: You deploy a package to your compat-100 tenant and the run reports success — exit code 0, Successfully Quenched — but the table you seeded comes up empty. The same package delivers rows everywhere else. What happened, and what's the one-line fix?

The DataDelivery for that table is encoded as JSON — the default when ContentEncoding is left blank — and JSON data delivery requires SQL Server compatibility level 130. Below that, under the default warn policy, the delivery is skipped rather than failing the run: the table still gets created, the deploy still exits clean, and the rows simply never land. Unlike the model ingest from the prior module, the engine does not auto-switch your data’s encoding for you — ContentEncoding is never inferred. The fix is to re-encode that delivery by hand, pointing ContentFile at an XML payload and setting ContentEncoding to Xml. That one property lands the rows on the legacy tier and keeps working everywhere above it. (Set the target’s UnsupportedFeaturePolicy to fail instead of warn and the same mismatch stops the run outright rather than exiting clean with an empty table — useful if a silent skip is the scarier outcome.)

Check yourself: A teammate sets ContentEncoding to Xml on a table's DataDelivery targeting PostgreSQL, expecting the same legacy-tier escape hatch this recipe showed on SQL Server. The deploy succeeds and all the rows land. They conclude PostgreSQL has the same compatibility cliff SQL Server does. Are they right, and when is declaring Xml there actually worth doing?

They’re wrong about the cliff, even though the deploy worked. PostgreSQL has no compatibility wall — its JSON support reaches back to its own oldest supported floor, so a JSON delivery shreds at every version SchemaSmith targets there. Nothing was escaped. From 2.5.0 the encoding is simply accepted on every engine: PostgreSQL shreds the XML natively with xmltable, and MySQL and MariaDB convert the payload to JSON once up front and run it through their ordinary JSON row source.

So the deploy succeeding proves the encoding is portable, not that it was needed. On SQL Server, Xml buys version reach — below compat 130 it is the only wire format that parses at all. On the other three it buys exactly one thing: authoring uniformity. A package that must serve a below-130 SQL Server tier alongside PostgreSQL, MySQL, or MariaDB can now carry ONE DataDelivery block and ONE payload file for all of them, instead of an XML declaration for the laggard and a JSON one for its siblings.

That’s the whole test. If the same package has to reach a below-130 SQL Server target, declaring Xml everywhere keeps the declaration shared. If it doesn’t, leave ContentEncoding out — JSON is the default, every engine reads it most directly, and it’s far easier to review in a diff.

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. Extract spatial data from SQL Server, or set the SRID on the destination yourself.

Mark this lesson complete