The oldest tier

There’s a database in your farm so old that OPENJSON won’t even parse on it. It’s under contract, it’s not moving this quarter, and the same package that lands clean on your 2022 box has to land on that too.

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

We’ve come all the way down the arc. The engine adapts its own DDL (Module 1). You gate the scripts it can’t touch (Module 2). You pick between two valid shapes, or gate on state the server can’t see (Module 3). This module is the layer underneath all of it — the wire format bends. Down at the oldest tier, the way the model itself travels changes, and you need to know where that’s automatic and where it lands in your hands. Let’s kindle the forge.

The cliff is compat 130, and it’s about JSON

Here’s the fact the whole module turns on: OPENJSON needs SQL Server database compatibility level 130. Not the server binary — the database’s compat level. Below 130, a query that so much as mentions OPENJSON doesn’t run slow or return empty. It parse-errors before it starts.

That matters because SchemaSmith’s own model ingest is built on it. When you deploy tables, the engine hands its built-in SchemaSmith.TableQuench proc a serialized copy of your model and shreds it back out. Above the cliff, it reads that payload as JSON. Below it, JSON is a non-starter — so it reads the same payload as XML instead.

Part one — the engine’s switch, which you never see

This first part is the easy half, because SchemaSmith does it for you and never asks.

The knob is Target:CompatEncoding, and it takes three values:

auto (the default) — XML when the database is below compat 130, JSON otherwise.
legacy — force XML.
modern — force JSON.

You’ll almost never set it. auto is right virtually always, and it’s SQL-Server-only — the other engines don’t have a compatibility level to gate on. The important part: the built-in proc keeps the same name on both tiers. SchemaSmith swaps the body at kindle time — same SchemaSmith.TableQuench, different guts, chosen by the target’s compat level. Deploy the one package to your compat-160 box and your compat-100 laggard and both take it. One reads its model as JSON, one as XML, and nothing in the log makes you care which.

That’s the invisible half. It’s automatic, it’s correct, and it’s not where your attention goes.

Part two — the switch that IS yours

Now the half that’s on you.

Those model-payload tokens SchemaSmith fills for you — {{TableSchema}} and its family — are documented for a reason: you’re meant to shred them in your own scripts. Serialize your model, hand it to your own SQL, do something with it. And the second you write OPENJSON over {{TableSchema}} in a script bound for the oldest tier, you hit the same cliff the engine did. Your script parse-errors at compat 100.

So every model-payload token ships an always-present XML twin. {{TableSchema}} has {{TableXml}}. {{IndexedViewSchema}} has {{IndexedViewXml}}. Same model, encoded as XML, shreddable with XQuery at any compat level. Both tokens are always there — you pick which to shred.

And picking is exactly Module 2’s move, turned on your own code. You write two variants of the script, folder-gated on {{CompatibilityLevel}}:

Modern — where compat is 130 or better, shred the JSON:

-- Shred/Modern — ShouldApplyExpression: {{CompatibilityLevel}} >= 130
DECLARE @model NVARCHAR(MAX) = N'{{TableSchema}}';   -- a JSON array: [{...},{...}]
DELETE FROM dbo.TableCatalog;   -- [ALWAYS] script: clear then repopulate, so a re-run stays idempotent
INSERT INTO dbo.TableCatalog (TableName, ColumnName, IsNullable, Encoding)
SELECT tbl.[Name], col.[ColumnName], col.[IsNullable], N'JSON (OPENJSON)'
FROM OPENJSON(@model)
       WITH ([Name] NVARCHAR(128) '$.Name', [Columns] NVARCHAR(MAX) '$.Columns' AS JSON) AS tbl
CROSS APPLY OPENJSON(tbl.[Columns])
       WITH ([ColumnName] NVARCHAR(128) '$.Name', [IsNullable] BIT '$.Nullable') AS col;

Legacy — where compat is below 130, shred the XML twin:

-- Shred/Legacy — ShouldApplyExpression: {{CompatibilityLevel}} < 130
DECLARE @model xml = N'{{TableXml}}';                -- <Tables><Table>...</Table></Tables>
DELETE FROM dbo.TableCatalog;   -- [ALWAYS] script: clear then repopulate, so a re-run stays idempotent
INSERT INTO dbo.TableCatalog (TableName, ColumnName, IsNullable, Encoding)
SELECT t.n.value('(Name/text())[1]', 'nvarchar(128)'),
       c.col.value('(Name/text())[1]', 'nvarchar(128)'),
       CONVERT(BIT, CASE LOWER(c.col.value('(Nullable/text())[1]', 'varchar(8)'))
                      WHEN 'true' THEN 1 WHEN 'false' THEN 0 END),
       N'XML (.nodes/.value)'
FROM @model.nodes('/Tables/Table') AS t(n)
CROSS APPLY t.n.nodes('Columns') AS c(col);

Each one writes a row per shredded column into an audit table, dbo.TableCatalog, tagged with the encoding it used. So the audit table itself is the receipt — you don’t guess which variant fired, you read it back.

Deploy to learn_2022 (compat 160) and the Modern folder applies, the Legacy folder is skipped:

[localhost,11433].[learn_2022]   Skipping folder 'Shred/Legacy' — ShouldApplyExpression evaluated false
[localhost,11433].[learn_2022]     Quenching Shred/Modern\PopulateTableCatalog [ALWAYS].sql
[localhost,11433].[learn_2022] Successfully Quenched

TableName     ColumnName   IsNullable  Encoding
------------  -----------  ----------  ---------------
TableCatalog  CatalogId    0           JSON (OPENJSON)
TableCatalog  TableName    0           JSON (OPENJSON)
TableCatalog  ColumnName   0           JSON (OPENJSON)
TableCatalog  IsNullable   1           JSON (OPENJSON)
TableCatalog  Encoding     0           JSON (OPENJSON)
Widget        WidgetId     0           JSON (OPENJSON)
Widget        Name         0           JSON (OPENJSON)
Widget        IsActive     1           JSON (OPENJSON)
Widget        Notes        1           JSON (OPENJSON)

Deploy the identical package to learn_2008 (compat 100) and it’s the mirror — the Modern folder gates off, the Legacy XML shred runs instead:

[localhost,11433].[learn_2008]   Skipping folder 'Shred/Modern' — ShouldApplyExpression evaluated false
[localhost,11433].[learn_2008]     Quenching Shred/Legacy\PopulateTableCatalog [ALWAYS].sql
[localhost,11433].[learn_2008] Successfully Quenched

TableName     ColumnName   IsNullable  Encoding
------------  -----------  ----------  --------------------
TableCatalog  CatalogId    0           XML (.nodes/.value)
TableCatalog  TableName    0           XML (.nodes/.value)
TableCatalog  ColumnName   0           XML (.nodes/.value)
TableCatalog  IsNullable   1           XML (.nodes/.value)
TableCatalog  Encoding     0           XML (.nodes/.value)
Widget        WidgetId     0           XML (.nodes/.value)
Widget        Name         0           XML (.nodes/.value)
Widget        IsActive     1           XML (.nodes/.value)
Widget        Notes        1           XML (.nodes/.value)

The reader’s XML shred is a faithful twin of the JSON one; the payload changed shape, the result did not.

Same rows, same nullability, the identical model — only the Encoding tag differs. That tag is your proof of which tongue your script read the model in on which tier. (learn_2016 sits at compat 130, so 130 >= 130 puts it on the Modern side with learn_2022 — the cliff is at 130, not 160.)

The footgun the XML path charges

Look hard at the IsNullable line in the two variants, because the difference is a real trap.

In JSON, Nullable is a genuine boolean. OPENJSON ... WITH ([IsNullable] BIT '$.Nullable') reads it straight into a BIT. Nothing to it.

In XML, every scalar is text. That same flag comes across as the literal string 'true' or 'false'. Reach for the obvious CAST('true' AS BIT) and SQL Server throws:

Msg 245, Level 16, State 1
Conversion failed when converting the varchar value 'true' to data type bit.

So the XML shred routes it through an explicit CASE LOWER(...) WHEN 'true' THEN 1 WHEN 'false' THEN 0 END before CONVERT(BIT, ...). It’s the exact conversion SchemaSmith’s own built-in XML ingest uses — no coincidence. When you hand-write a legacy shred, boolean-as-text is the tax the old encoding charges, and it’s cheaper to know it now than to meet Msg 245 in a deploy.

The choice is SQL-Server-only — and that’s honest

One scope note, because it’s the kind of thing that looks like a gap and isn’t.

This JSON-vs-XML fork is SQL Server only. PostgreSQL has had JSON functions since 9.2, so its {{TableSchema}} shreds at every version you’d run. MySQL and MariaDB below their JSON cliff fall back to JSON_EXTRACT — still JSON. SQL Server is the only engine whose oldest supported tier can’t parse JSON at all, so it’s the only place the XML twin is a needed alternative rather than a portability convenience. The twins are produced on every engine for one consistent authoring surface; only SQL Server ever has to reach for them.

So the fork not traveling isn’t a parity hole — it’s an accurate map of where the encoding cliff actually sits. And the bigger promise still holds farm-wide: one package, every floor. The same table package in the lab deploys clean to the SQL Server compat-100 tier, PostgreSQL 12, MySQL 5.7, and MariaDB 10.2 — every floor takes it, the encoding handled for each.

(One thing this module’s model shred doesn’t cover: the same cliff hits your seed data, and there the engine won’t bend for you the way it just bent for the model. The XML data-delivery recipe covers the one knob for it.)

[localhost,11433].[learn_2008] Successfully Quenched     # SQL Server 2008-floor (compat 100)
[localhost].[learn]            Successfully Quenched     # PostgreSQL 12 (port 15433)
[localhost].[learn]            Successfully Quenched     # MySQL 5.7 (port 13316)
[localhost].[learn]            Successfully Quenched     # MariaDB 10.2 (port 13317)

An honest word on the legacy round-trip

One caveat so nobody over-claims. The XML twin carries the structured model faithfully — schemas, tables, columns, indexes, all of it. What a SchemaTongs re-extract on the legacy tier does not bring back through XML is the free-form Extensions bag — arbitrary author-supplied metadata. So on the oldest tier the schema round-trips whole; loose Extensions metadata may not. Know it, don’t promise past it.

What just happened

You went to the bottom of the farm and watched the wire format bend. The engine’s own model ingest switches JSON to XML by itself below compat 130 — Target:CompatEncoding set to auto, same proc name, swapped body, invisible. And where the choice is genuinely yours — shredding a model-payload token in your own SQL — you paired OPENJSON on {{TableSchema}} above the cliff with .nodes()/.value() on the always-present {{TableXml}} twin below it, folder-gated on {{CompatibilityLevel}}, each stamping the audit table with the encoding it used. You paid the boolean-as-text tax the XML path charges, and you saw why the fork is SQL-Server-only without a single parity hole.

Automatic where it can be. Yours where it must be. And one package still lands on every floor.

Subscribe and stick around. Next module — retiring the gates. The oldest tier finally upgrades, and every gate you’ve built across this course turns into debt with a payoff date: you delete the legacy variants, raise MinimumVersion so pre-flight refuses the tier you stopped writing for, and the whole scheme retires itself — which is exactly what made it worth building.

Until then, may your model read true in either tongue, and the words you shred by hand always match the ones the engine shreds for you.

— Forge

Check yourself: You've got a script that shreds the TableSchema model-payload token with OPENJSON to build an audit table, and it works great on your compat-160 and compat-130 databases. You ship it to the one tenant still at compat 100 and the deploy blows up before the script does anything. What broke, and how do you fix it so the one package still lands on every tier?

OPENJSON requires SQL Server database compatibility level 130. Below it — your compat-100 tenant — a script that even mentions OPENJSON parse-errors before it runs. The fix is the always-present XML twin of the token: {{TableSchema}} has {{TableXml}}, carrying the identical model as <Tables><Table>…</Table></Tables>, shreddable with XQuery (.nodes()/.value()) at any compat level. Write two variants of the script folder-gated on {{CompatibilityLevel}} — the OPENJSON form where >= 130, the XML form where < 130 — exactly the gating pattern from Module 2 turned on your own code. Note this is the reader’s shred: SchemaSmith’s own built-in model ingest already makes the same JSON→XML switch automatically via Target:CompatEncoding (auto), so the tables deploy either way; the gate is only for the SQL you wrote.

Check yourself: In your legacy XML shred you pull each column's Nullable flag and try CAST(... AS BIT), and SQL Server throws 'Conversion failed when converting the varchar value true to data type bit.' The JSON version of the same shred never had this problem. Why, and what's the right conversion?

In XML every scalar is text, so the Nullable flag arrives as the literal string 'true'/'false', and CAST('true' AS BIT) errors (Msg 245) because there’s no implicit text-to-bit conversion for those words. In JSON the flag is a real boolean, so OPENJSON ... WITH ([IsNullable] BIT '$.Nullable') reads it straight into a BIT — no conversion needed. The right fix for the XML path is an explicit map before converting: CONVERT(BIT, CASE LOWER(x) WHEN 'true' THEN 1 WHEN 'false' THEN 0 END). It’s the same conversion SchemaSmith’s own built-in XML ingest uses — boolean-as-text is the standing tax of the legacy encoding, and it only bites when you hand-write the shred.

Mark this lesson complete