Reading Complex XML in SQL Server — Shredding a Thousand Attributes Generically Instead of Looping for Hours

Published Oct 11, 2026
by

A catalog of around 3000 product configurations arrives as XML. Depending on the equipment, a configuration carries different and additional attributes, around a thousand across all variants. In an earlier project, the evaluation of such a catalog ran in application code: a loop over the object types, inside it a loop over the attributes, plus lists in memory that grew with every iteration. The program took several hours. Where exactly the time went can no longer be said in hindsight. What is certain is what the design demanded: code per type, a separate lookup per attribute, and a schema that had to be in your head beforehand. This article shows how to shred XML in SQL Server without knowing the attributes in advance: load it raw, shred it into a path/value table that holds one row per value with its path in the document, read the schema off the data, and let the database generate the views per type. In the measurement of this article that takes not hours but around 15 seconds for a catalog of the same size.

At a glance

  • Why loops over object types and attributes get so expensive with a thousand attributes, and what the database route does differently: the attributes become data rows instead of code.
  • How the catalog is loaded into SQL Server as a single XML value and then split into 3000 table rows, one per configuration. From these rows, a query in which no attribute name appears fetches every value together with its path in the document, for example /Drivetrain/Gears with the value 14. The result is 1.5 million such rows of path and value, and the whole route takes around 15 seconds in the measurement instead of hours.
  • How an inventory built from these rows makes required, optional and variant attributes per type readable, without anyone having known the attributes beforehand.
  • How the database generates views with more than 600 columns from the inventory itself, and where the limits of the method are: column limits, typing, very large files. What it requires is little: the element of the sub-documents, an upper bound for the depth, and lists that repeat uniformly in at least one configuration.

Prerequisites: Docker and SQL Server 2022 as a container (Developer Edition). The scripts run with sqlcmd, the command-line client that ships with the container. The call is at the start of the section “Load raw”. The sample catalog, its generator and the scripts come in four downloads at the end of the article: the XML route and the JSON route, each for SQL Server and for Postgres. Postgres 18.6 serves for the bridge at the end.

Contents

The catalog: three types, one drivetrain with two faces

The example is a bicycle configurator with 3000 configurations. There are three object types: road bike, trekking bike and cargo bike. The three types share the groups General, Frame, Brakes and Wheels, and already here they resemble each other more than they differ. What the Drivetrain contains depends on its XML attribute kind. With kind="chain" it holds chainrings, sprockets, rear derailleur, front derailleur, gear ratios and chainline. With kind="hub" it holds gears, gear range, belt, hub type and coaster brake. The same group, but different attributes depending on the variant. The Brakes also carry their design as an XML attribute, type, and not as a child element. Lighting and Suspension are optional groups that a road bike rarely has and a cargo bike almost always. Two groups are lists. Accessories contains zero to four Part elements with name, weight and a standard-equipment flag. Wheels always contains two Wheel elements, one for the front and one for the rear, with rim, tyre and spoke count.

“Attribute” in this article means a business characteristic of a configuration, such as the number of gears or the material of the frame. In the XML such a characteristic is usually an element and only rarely an XML attribute. Where the XML construct is meant, the text says XML attribute explicitly.

To reach the order of a thousand attributes, every configuration additionally carries twelve of twenty parameter groups with 48 values each, every single one of which is present with a probability of eighty percent. These groups are simply called G01 to G20 and are filler. They are there so that the measurements have the right order of magnitude, not because they mean anything. Which type carries which groups is distributed so that the types overlap but do not coincide.

This is what one configuration looks like, shortened:

  1: <Configuration id="C000001" type="cargo_bike">
  2:   <General>
  3:     <Model>CargoBike-214</Model>
  4:     <ModelYear>2019</ModelYear>
  5:     <Weight>13.25</Weight>
  6:     <Price>4576.94</Price>
  7:   </General>
  8:   <Frame>
  9:     <Material>Aluminum</Material>
 10:     <Size>56</Size>
 11:   </Frame>
 12:   <Drivetrain kind="hub">
 13:     <Gears>14</Gears>
 14:     <GearRange>371</GearRange>
 15:     <Belt>yes</Belt>
 16:   </Drivetrain>
 17:   <Brakes type="Disc hydraulic">
 18:     <Rotor_front>160</Rotor_front>
 19:     <Pads>sintered</Pads>
 20:   </Brakes>
 21:   <Wheels>
 22:     <Wheel><Position>front</Position><Rim>584x23</Rim><Tyre>32-622</Tyre><Spokes>24</Spokes></Wheel>
 23:     <Wheel><Position>rear</Position><Rim>559x25</Rim><Tyre>47-622</Tyre><Spokes>36</Spokes></Wheel>
 24:   </Wheels>
 25:   <Lighting>
 26:     <Front_Lumen>70</Front_Lumen>
 27:     <PowerSource>HubDynamo</PowerSource>
 28:   </Lighting>
 29:   <Accessories>
 30:     <Part><Name>BottleCage</Name><Weight>275</Weight><Standard>yes</Standard></Part>
 31:     <Part><Name>Speedometer</Name><Weight>441</Weight><Standard>yes</Standard></Part>
 32:   </Accessories>
 33:   <Parameter>
 34:     <G09><P02>552.04</P02><P04>861.71</P04><P06>B</P06></G09>
 35:   </Parameter>
 36: </Configuration>

The catalog does not come from a real project but from a Python script, the generator in the download. The script rolls the dice for the type, the equipment and the values of every configuration, but starts the random generator with the same seed on every run. That is why every run produces exactly the same catalog, and all numbers in this article can be reproduced with it. The XML is 28 MB in size and contains a good 1.5 million values. The same data exists as JSON Lines, one line per configuration, in the notation produced by the Python package xmltodict: attributes as keys with @, repeated elements as arrays. Each of the two files comes with the generator in a download of its own, XML and JSON separately. The German version of this article uses the same catalog with German element names. The structure and all numbers are identical.

Why loops over variable attributes get expensive

The obvious design in application code looks like this: for every object type there is a class or a mapping table that knows its attributes. For every configuration, the code walks through the attributes of that type, looks up the node in the document, reads the value and stores it in a list. If a value turns up that the list does not know yet, the list grows. With three types and twenty attributes that is manageable. With a thousand attributes, some of which overlap and some of which exist in only one variant, it becomes code that rebuilds the structure of the catalog, line by line. Every new attribute is a code change. Every access to a node is a separate search through the document. And every list that grows with progress is searched a little more slowly with every iteration.

Where exactly the hours came from in that project can no longer be proven, and this article does not claim to know. It only claims that the design multiplies the costs: types times attributes times configurations, plus lists whose size depends on the order of processing. The general argument for why a loop per element loses against a query over the set is made in the article Set-based vs. row by row on another case study. Here it is about the special case where the loop does not even know what it is looping over, because the schema only exists in the data. The programming language is not the problem. A program that walks the tree once and emits every leaf with its path needs no schema and stays fast, and that is exactly what the twin of this article shows in Python. It only gets expensive through a design that searches the document again for every attribute.

The database route turns the task around. It does not know the attributes and does not need to. What it requires is little: the element under which the configurations sit, and an upper bound for the depth of the tree. Added to that is an assumption about lists: it recognises them only when an element name repeats within them in at least one configuration. It loads the document raw, shreds it with generic queries in which no attribute name appears into rows of path and value, and only afterwards does anyone look at which paths exist at all. The thousand attributes are then a thousand different values in one column, not a thousand lines of code. The schema is counted, not known. And the views that a consumer wants to see at the end are built by the database from this count. Architecturally this is ELT: load raw, then transform in SQL. What distinguishes that from classic ETL is explained in ETL vs. ELT.

The following diagram shows the whole route with the tables that come into being in the next sections, and with the numbers from the sample catalog.

Flow diagram of the database route: the file configs.xml (28 MB) is loaded as one xml value into stage.raw_xml and split into 3000 rows in stage.config, one per configuration. Three passes read from it: pass 1 fetches 1,516,476 leaf elements, pass 2 computes ordinals for 12,095 repeated elements, a third pass fetches 6000 XML attributes. The three results flow together into stage.leaf with 1,522,476 rows and 1024 paths, from which stage.inventory and the generated view dbo.vw_trekking_bike with 621 columns are built.

Load raw: one row per configuration

All SQL Server scripts of this article run against a Docker container. The catalog and the scripts are copied with docker cp to /tmp in the container, which is where the paths in the scripts point. Every file is run with sqlcmd, which ships with the image: the switch -i names the file, -C accepts the self-signed certificate of the container, and -I turns on QUOTED_IDENTIFIER, which the XML methods require. For the remaining files only the file name changes.

  1: docker run -d --name 147-mssql-xml-en -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD='S3cret!Pass' `
  2:     mcr.microsoft.com/mssql/server:2022-latest
  3: docker exec 147-mssql-xml-en /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P 'S3cret!Pass' `
  4:     -Q "CREATE DATABASE catalog"
  5: docker cp .\configs.xml 147-mssql-xml-en:/tmp/configs.xml
  6: docker cp .\147001-mssql-xml-load.sql 147-mssql-xml-en:/tmp/147001-mssql-xml-load.sql
  7: docker exec 147-mssql-xml-en /opt/mssql-tools18/bin/sqlcmd -C -I -S localhost -U sa -P 'S3cret!Pass' `
  8:     -d catalog -i /tmp/147001-mssql-xml-load.sql

The first step loads the whole file as a single value of type xml. OPENROWSET in SINGLE_BLOB mode returns the file as varbinary, and CONVERT to xml parses it once into SQL Server’s internal representation. Right after that the document is split: nodes() returns one row per Configuration element, value() fetches the two XML attributes as columns, and query('.') stores the fragment as an xml value of its own.

  1: -- --------------------------------------------------------------------------------
  2: -- 147001: Load the catalog and split it into one row per configuration (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: -- The XML methods require QUOTED_IDENTIFIER ON; sqlcmd starts with OFF (switch -I).
  5: SET QUOTED_IDENTIFIER ON;
  6: GO
  7: IF SCHEMA_ID('stage') IS NULL EXEC ('CREATE SCHEMA [stage]');
  8: GO
  9: -- --------------------------------------------------------------------------------
 10: -- The whole document as a single xml value
 11: -- --------------------------------------------------------------------------------
 12: DROP TABLE IF EXISTS [stage].[raw_xml];
 13: CREATE TABLE [stage].[raw_xml]
 14: (
 15:     doc   xml NOT NULL
 16: );
 17: 
 18: INSERT INTO [stage].[raw_xml] (doc)
 19: SELECT
 20:    CONVERT(xml, T01.BulkColumn)
 21: FROM
 22:    OPENROWSET(BULK '/tmp/configs.xml', SINGLE_BLOB) AS T01;
 23: 
 24: -- --------------------------------------------------------------------------------
 25: -- One row per configuration: key, type and the fragment as xml
 26: -- --------------------------------------------------------------------------------
 27: DROP TABLE IF EXISTS [stage].[config];
 28: SELECT
 29:     T02.n.value('@id',  'varchar(20)') AS config_id
 30:    ,T02.n.value('@type', 'varchar(20)') AS config_type
 31:    ,T02.n.query('.')                   AS node
 32: INTO [stage].[config]
 33: FROM
 34:    [stage].[raw_xml] T01
 35:    CROSS APPLY T01.doc.nodes('/Catalog/Configuration') AS T02(n);
 36: 
 37: SELECT
 38:     config_type
 39:    ,COUNT(*) AS configs
 40: FROM
 41:    [stage].[config]
 42: GROUP BY
 43:    config_type;
 44: -- cargo_bike      1025
 45: -- road_bike        995
 46: -- trekking_bike    980

The split is the most important step of the whole method, for a reason that only becomes visible later. All subsequent XQuery expressions then run against fragments of around 9 KB instead of a document of 28 MB. What that is worth is shown more drastically by Postgres than by SQL Server: there, the same generic query over the whole document was still running after 36 minutes and could not even be cancelled. More on that in the Postgres bridge.

One trap right at the start: the XML methods nodes(), value() and query() require QUOTED_IDENTIFIER ON, alongside further session options such as ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS, which are already set in sqlcmd. The tool sqlcmd, however, starts with QUOTED_IDENTIFIER OFF, and the error message does name the option, but does not say which construct in the statement needs it. Instead it lists all the features that depend on it. The switch -I on the sqlcmd call takes care of that, alternatively the first line in the script.

Shred generically: every leaf with its path

The goal is a table with four columns: configuration, type, path and value. In the SQL Server world this breaking of a document into rows is called XML shredding, and the tools for it are the methods nodes() and value() of the xml type. The values sit in the leaves of the tree, that is, in elements without child elements, and additionally in XML attributes. An XML attribute can hang on any element, including one with children, like kind on the Drivetrain. The path is the chain of element names from the Configuration element down to the leaf. For an XML attribute it ends with @ and the attribute name. From the excerpt above this yields rows such as /Frame/Material with the value Aluminum, /Drivetrain/Gears with the value 14 or /Drivetrain/@kind with the value hub.

This happens in two passes over all 3000 fragments, called pass 1 and pass 2 in what follows. The reason for the split are the lists. A configuration with two accessory parts has two elements with the same path /Accessories/Part/Name. To tell them apart, every element would have to carry its position among its same-named siblings, that is, whether it is the first or the second part. This position is called ordinal in what follows and appears in square brackets in the path, for example /Accessories/Part[2]/Name. Computing it is expensive in SQL Server: for each of the 1.5 million leaves the XQuery engine would have to count the siblings before it, and that raises the runtime in the measurement from around two to around 40 seconds. Pass 1 therefore fetches all values with their path, but without positions. From its result, an ordinary grouping reveals at which positions elements repeat at all. In the catalog these are Part under Accessories and Wheel under Wheels. Pass 2 then computes the positions only for these positions, that is, for around 12,000 elements instead of 1.5 million. XML attributes are not elements and are skipped by both passes. They get a short pass of their own.

Pass 1: all leaves, no positions

The path expression /Configuration//*[not(*)] matches every element in the fragment that has no child element, at any depth. The names of its ancestors come from the parent axis: local-name(..) is the name of the parent element, local-name(../..) that of the grandparent, and so on. In XQuery, SQL Server supports the axes child, descendant, descendant-or-self, parent, attribute and self, but not ancestor. That is why the depth has to be chosen as a fixed number. The catalog has at most three levels below the configuration, so four name columns suffice with one column in reserve: the fourth always holds Configuration. That is exactly what can be checked. A leaf for which Configuration appears in none of the ancestor columns lies deeper than the reserve, and from the fifth level on its path would be cut off at the front without any error message pointing it out. Whoever has deeper documents adds columns.

  1: -- --------------------------------------------------------------------------------
  2: -- 147002: Pass 1 - every leaf element with the names of its ancestors (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: -- One pass over all configurations. The parent axis (..) yields the ancestor
  5: -- names; positions are deliberately NOT computed here.
  6: SET QUOTED_IDENTIFIER ON;
  7: GO
  8: DROP TABLE IF EXISTS [stage].[leaf_raw];
  9: SELECT
 10:     T01.config_id
 11:    ,T01.config_type
 12:    ,T02.n.value('local-name(.)',        'varchar(100)') AS name0   -- the leaf itself
 13:    ,T02.n.value('local-name(..)',       'varchar(100)') AS name1   -- parent
 14:    ,T02.n.value('local-name(../..)',    'varchar(100)') AS name2   -- grandparent
 15:    ,T02.n.value('local-name(../../..)', 'varchar(100)') AS name3   -- great-grandparent
 16:    ,T02.n.value('.',                    'varchar(200)') AS value
 17: INTO [stage].[leaf_raw]
 18: FROM
 19:    [stage].[config] T01
 20:    CROSS APPLY T01.node.nodes('/Configuration//*[not(*)]') AS T02(n);
 21: 
 22: SELECT
 23:    COUNT(*) AS leaves
 24: FROM
 25:    [stage].[leaf_raw];
 26: -- 1516476

This single pass delivers all 1,516,476 leaf elements of the 3000 configurations in around two seconds. Lines 12 to 15 fetch the four names, line 16 the value, and that is all. What is deliberately missing is the position of an element among its siblings. It would be needed to tell the two Part elements in the accessories apart. The obvious XQuery expression for it is let $i := . return count(../*[. << $i]) + 1: count the siblings that precede me in the document. With two such expressions, one for the leaf and one for its parent element, the same query takes twenty times as long instead of around two seconds, measured 1.8 against 40.2 seconds. For each of the 1.5 million leaves SQL Server then walks the sibling list and compares document positions, and exactly this evaluation is expensive in the XQuery engine. Computing the position across the board is therefore not an option. It is computed in the second pass only where it is needed.

XML attributes

XML attributes are not elements and do not appear in pass 1, regardless of whether they hang on a leaf or on an element with children. They get a short pass of their own. In SQL Server 2022 the method nodes() also returns attribute nodes, the path /Configuration//@* matches all XML attributes in the fragment, and the parent axis leads from the XML attribute to the element that carries it. The two XML attributes of the Configuration element itself are already columns and are excluded.

  1: -- --------------------------------------------------------------------------------
  2: -- 147003: Attributes - nodes() also returns attribute nodes (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: -- The attributes of the configuration itself (id, type) are already columns in
  5: -- [stage].[config] and are excluded here.
  6: SET QUOTED_IDENTIFIER ON;
  7: GO
  8: DROP TABLE IF EXISTS [stage].[leaf_attr];
  9: SELECT
 10:     T01.config_id
 11:    ,T01.config_type
 12:    ,T02.n.value('local-name(.)',        'varchar(100)') AS attr_name
 13:    ,T02.n.value('local-name(..)',       'varchar(100)') AS name1   -- element carrying the attribute
 14:    ,T02.n.value('local-name(../..)',    'varchar(100)') AS name2
 15:    ,T02.n.value('local-name(../../..)', 'varchar(100)') AS name3
 16:    ,T02.n.value('.',                    'varchar(200)') AS value
 17: INTO [stage].[leaf_attr]
 18: FROM
 19:    [stage].[config] T01
 20:    CROSS APPLY T01.node.nodes('/Configuration//@*') AS T02(n)
 21: WHERE
 22:    T02.n.value('local-name(..)', 'varchar(100)') <> 'Configuration';
 23: 
 24: SELECT
 25:     name1
 26:    ,attr_name
 27:    ,COUNT(*) AS n
 28: FROM
 29:    [stage].[leaf_attr]
 30: GROUP BY
 31:     name1
 32:    ,attr_name
 33: ORDER BY
 34:     name1
 35:    ,attr_name;
 36: -- Brakes       type   3000
 37: -- Drivetrain   kind   3000

The catalog has two further XML attributes, kind on the drivetrain and type on the brakes, and the pass takes half a second.

Pass 2: ordinals only where something repeats

Without positions, the two Part elements of a configuration fall onto the same path /Accessories/Part/Name. For the inventory in the next section that is actually fine, for a view with one column per path it is not. The solution has two steps. First, a set-based query reads off pass 1 which parent elements repeat at all: if, within one configuration, more than one leaf with the same name sits under the same parent path, then either the parent element repeats, or the leaf itself repeats within the parent element. Both need ordinals. Which of the two cases applies, the grouping does not reveal, because in pass 1 both look the same. The template below solves the first one. The second one is shown by the inventory after pass 2: the path then carries more than one value in a single configuration.

  1: -- --------------------------------------------------------------------------------
  2: -- 147004: Which parents repeat? Set-based, from pass 1 (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: -- A parent element repeats when more than one leaf of the same name sits under
  5: -- the same parent path within one configuration. The same query also finds
  6: -- leaves that repeat within a single parent.
  7: DROP TABLE IF EXISTS [stage].[repeated_parent];
  8: WITH
  9: CTE_leaf_count AS
 10: (
 11:    SELECT
 12:        config_id
 13:       ,name3
 14:       ,name2
 15:       ,name1
 16:       ,name0
 17:       ,COUNT(*) AS n
 18:    FROM
 19:       [stage].[leaf_raw]
 20:    GROUP BY
 21:        config_id
 22:       ,name3
 23:       ,name2
 24:       ,name1
 25:       ,name0
 26: )
 27: SELECT DISTINCT
 28:     CASE WHEN name2 = 'Configuration' THEN ''
 29:          WHEN name3 = 'Configuration' THEN '/' + name2
 30:          ELSE '/' + name3 + '/' + name2 END AS parent_path
 31:    ,name1 AS parent_name
 32: INTO [stage].[repeated_parent]
 33: FROM
 34:    CTE_leaf_count
 35: WHERE
 36:    n > 1;
 37: 
 38: SELECT
 39:     parent_path
 40:    ,parent_name
 41: FROM
 42:    [stage].[repeated_parent]
 43: ORDER BY
 44:    parent_path;
 45: -- /Accessories   Part
 46: -- /Wheels        Wheel

The query finds two positions, /Accessories with the child Part and /Wheels with the child Wheel, and takes under a second for that. Only now does the expensive position computation come into play, and only for the nodes that this detection has delivered. For every position found, a template yields a SELECT that fetches the affected parent elements with nodes(), computes their position among their same-named siblings and reads the leaves below them with their own position. At such a position every element gets its ordinal, even when a configuration has only a single element there. A configuration with three parts gets /Accessories/Part[1]/Name to /Accessories/Part[3]/Name, one with exactly one part gets /Accessories/Part[1]/Name. If the single part stayed without an ordinal, the first element of the same list would sit on two different paths depending on the configuration. The inventory would then count one list as two things, and the path without ordinal would end up as a column in the type’s view, filled only for the configurations with exactly one part. The two wheels of every configuration are accordingly called /Wheels/Wheel[1]/Rim and /Wheels/Wheel[2]/Rim.

  1: -- --------------------------------------------------------------------------------
  2: -- 147005: Pass 2 - ordinals only for the parents that repeat (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: -- For every row of [stage].[repeated_parent] the template yields one SELECT.
  5: -- At such a position every element gets its ordinal, even when a configuration
  6: -- has only a single element there: a list stays a list, and its first element
  7: -- sits on the same path in every configuration.
  8: -- The expensive position expression runs only over the few affected nodes,
  9: -- not over all leaves.
 10: -- If leaves repeat directly within one parent element, the template gets the
 11: -- same expression once more on leaf level (T03). That costs a multiple of the
 12: -- runtime of pass 2 and is not needed here. Whether the case exists is shown
 13: -- by the inventory: n_values greater than n_configs.
 14: SET QUOTED_IDENTIFIER ON;
 15: GO
 16: DECLARE @template nvarchar(max) = N'
 17: SELECT
 18:     T01.config_id
 19:    ,T01.config_type
 20:    ,''{path}/{name}''
 21:     + T02.n.value(''let $p := .
 22:                     return concat("[", string(count(../*[local-name() = "{name}"][. << $p]) + 1), "]")'', ''varchar(10)'')
 23:     + ''/'' + T03.n.value(''local-name(.)'', ''varchar(100)'') AS path
 24:    ,T03.n.value(''.'', ''varchar(200)'') AS value
 25: FROM
 26:    [stage].[config] T01
 27:    CROSS APPLY T01.node.nodes(''/Configuration{path}/{name}'') AS T02(n)
 28:    CROSS APPLY T02.n.nodes(''./*[not(*)]'') AS T03(n)';
 29: 
 30: DECLARE @sql nvarchar(max);
 31: 
 32: SELECT
 33:    @sql = STRING_AGG(
 34:              CAST(REPLACE(REPLACE(@template, N'{path}', parent_path), N'{name}', parent_name) AS nvarchar(max))
 35:             ,N' UNION ALL ')
 36: FROM
 37:    [stage].[repeated_parent];
 38: 
 39: DROP TABLE IF EXISTS [stage].[leaf_pass2];
 40: CREATE TABLE [stage].[leaf_pass2]
 41: (
 42:     config_id     varchar(20)  NOT NULL
 43:    ,config_type   varchar(20)  NOT NULL
 44:    ,path          varchar(400) NOT NULL
 45:    ,value         varchar(200) NULL
 46: );
 47: 
 48: INSERT INTO [stage].[leaf_pass2]
 49: EXEC sp_executesql @sql;
 50: 
 51: SELECT
 52:     path
 53:    ,COUNT(*) AS n
 54: FROM
 55:    [stage].[leaf_pass2]
 56: WHERE
 57:       path LIKE '%/Name'
 58:    OR path LIKE '%/Rim'
 59: GROUP BY
 60:    path
 61: ORDER BY
 62:    path;
 63: -- /Accessories/Part[1]/Name   2406
 64: -- /Accessories/Part[2]/Name   1831
 65: -- /Accessories/Part[3]/Name   1243
 66: -- /Accessories/Part[4]/Name    615
 67: -- /Wheels/Wheel[1]/Rim        3000
 68: -- /Wheels/Wheel[2]/Rim        3000

Pass 2 runs over 6095 Part elements and 6000 Wheel elements instead of 1.5 million leaves and takes under a second. The template applies at every position the detection reports, no matter how deep the repeated element sits in the fragment. Below it, however, it reads only the direct leaf children, the way Name, Weight and Standard sit directly under Part. If leaves repeat directly within one parent element, the template gets the same expression once more on leaf level. That costs around 15 seconds instead of one in the measurement, because navigating to the parent element from every leaf is expensive, but it stays limited to the positions found. Two forms the template does not cover. If there is a further level between the repeated element and its leaves, say Part/Product/Name, then the detection attributes the repetition to the intermediate element Product, and pass 2 assigns every Product the ordinal 1, because every Part has only one Product. The two names then land on the same path /Accessories/Part/Product[1]/Name. The same applies to lists within lists. Both cases need a further CROSS APPLY stage with the same pattern, and whether they occur is revealed by the inventory in the next section through a single metric. A third form escapes the detection itself, because it is a heuristic over the leaf names: it finds a list only when at least one leaf name repeats within it. If of two Part elements one carries only a Name and the other only a Weight, no leaf name repeats, and the two parts look like a single one in the path/value table. The inventory’s metric does not show this case. It stays hidden, however, only as long as not a single configuration carries the list uniformly. As soon as one does, the detection reports the position, and pass 2 assigns the ordinals in all configurations.

One shortcut looks tempting and is not one. ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) over the output of nodes() delivers the document order in practice, and in the measurement the ordinals derived from it matched the XQuery computation in all 12,095 cases. The documentation, however, does not guarantee this order, and the window function forces the optimizer into a plan that raises the runtime of pass 1 from around two to around 40 seconds. Both together are enough to leave the shortcut alone.

Merge

The path/value table comes from three sources: pass 1 without the leaves under the repeated parent elements, plus pass 2 and the XML attributes. The path is assembled from the name columns, up to the name Configuration, which marks the beginning. An index on type and path helps all the queries that now follow. At the end of the script stands a reconciliation: the number of leaves and XML attributes in the fragments must match the number of rows in the path/value table. This reconciliation only counts. Whether every value sits on the right path, it does not show.

  1: -- --------------------------------------------------------------------------------
  2: -- 147006: Merge - the path/value table (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: -- Pass 1 without the leaves under repeated parents, plus pass 2 and the
  5: -- attributes. Result: one row per leaf value with its path.
  6: -- The reconciliation at the end counts with XML methods and needs QUOTED_IDENTIFIER ON.
  7: SET QUOTED_IDENTIFIER ON;
  8: GO
  9: DROP TABLE IF EXISTS [stage].[leaf];
 10: SELECT
 11:     T01.config_id
 12:    ,T01.config_type
 13:    ,CASE WHEN T01.name1 = 'Configuration' THEN ''
 14:          WHEN T01.name2 = 'Configuration' THEN '/' + T01.name1
 15:          WHEN T01.name3 = 'Configuration' THEN '/' + T01.name2 + '/' + T01.name1
 16:          ELSE '/' + T01.name3 + '/' + T01.name2 + '/' + T01.name1 END
 17:     + '/' + T01.name0 AS path
 18:    ,T01.value
 19: INTO [stage].[leaf]
 20: FROM
 21:    [stage].[leaf_raw] T01
 22: WHERE
 23:    NOT EXISTS
 24:    (
 25:       SELECT
 26:          1
 27:       FROM
 28:          [stage].[repeated_parent] T02
 29:       WHERE
 30:              T02.parent_name = T01.name1
 31:          AND T02.parent_path = CASE WHEN T01.name2 = 'Configuration' THEN ''
 32:                                     WHEN T01.name3 = 'Configuration' THEN '/' + T01.name2
 33:                                     ELSE '/' + T01.name3 + '/' + T01.name2 END
 34:    )
 35: UNION ALL
 36: SELECT
 37:     config_id
 38:    ,config_type
 39:    ,path
 40:    ,value
 41: FROM
 42:    [stage].[leaf_pass2]
 43: UNION ALL
 44: SELECT
 45:     config_id
 46:    ,config_type
 47:    ,CASE WHEN name2 = 'Configuration' THEN ''
 48:          WHEN name3 = 'Configuration' THEN '/' + name2
 49:          ELSE '/' + name3 + '/' + name2 END
 50:     + '/' + name1 + '/@' + attr_name
 51:    ,value
 52: FROM
 53:    [stage].[leaf_attr];
 54: 
 55: CREATE INDEX ix_leaf_type_path ON [stage].[leaf] (config_type, path);
 56: 
 57: SELECT
 58:     COUNT(*)                  AS leaves
 59:    ,COUNT(DISTINCT config_id) AS configs
 60:    ,COUNT(DISTINCT path)      AS paths
 61: FROM
 62:    [stage].[leaf];
 63: -- 1522476   3000   1024
 64: 
 65: -- --------------------------------------------------------------------------------
 66: -- Reconciliation: leaves and attributes in the source against the rows of the table
 67: -- --------------------------------------------------------------------------------
 68: -- The attributes of the configuration itself (id, type) do not count. If the sum
 69: -- of the first two columns differs from the third, something was lost or
 70: -- duplicated while merging.
 71: SELECT
 72:     SUM(T01.node.value('count(/Configuration//*[not(*)])', 'int')) AS source_leaves
 73:    ,SUM(T01.node.value('count(/Configuration/*//@*)',      'int')) AS source_attributes
 74:    ,(SELECT COUNT(*) FROM [stage].[leaf])                           AS leaf_rows
 75: FROM
 76:    [stage].[config] T01;
 77: -- 1516476   6000   1522476
 78: 
 79: SELECT
 80:     path
 81:    ,value
 82: FROM
 83:    [stage].[leaf]
 84: WHERE
 85:        config_id = 'C000001'
 86:    AND path LIKE '/Drivetrain%'
 87: ORDER BY
 88:    path;
 89: -- /Drivetrain/@kind          hub
 90: -- /Drivetrain/Belt           yes
 91: -- /Drivetrain/CoasterBrake   no
 92: -- /Drivetrain/GearRange      371
 93: -- /Drivetrain/Gears          14
 94: -- /Drivetrain/HubType        N8

The result is 1,522,476 rows from 3000 configurations with 1024 distinct paths. The reconciliation adds up: 1,516,476 leaves and 6000 XML attributes in the source give the same number. The assignment of value to path was checked separately for this article, against a single pass that computes the position for each of the 1.5 million leaves. A comparison with EXCEPT over configuration, path and value yields zero differences in both directions. The ordinals of the parts turn three paths into twelve, those of the wheels four paths into eight, the rest are the 1004 paths of the catalog including the XML attributes /Drivetrain/@kind and /Brakes/@type. For the configuration from the excerpt, the table delivers under /Drivetrain exactly the six rows one would expect: the XML attribute and the five hub attributes.

In total, the XML route in SQL Server looks like this. The runtime per script was measured in a Docker container under Docker Desktop on a current desktop machine: Intel Core i7-14700KF with 28 logical cores, 62 GB RAM and an NVMe SSD under Ubuntu, of which 28 cores and 8 GB go to the Docker VM, the SQL Server container limited to 4 GB of memory, SQL Server 2022 build 16.0.4265.3 without further tuning, in the second run in the same container. The numbers are orders of magnitude on this one system, not benchmarks. For perspective: a three-year-old laptop with an Intel Core i7-1165G7 needed, in an earlier measurement with a smaller version of the catalog, around three times as long for the whole route and around ten times as long for the XQuery steps pass 1 and pass 2.

StepTime
Load as SINGLE_BLOB and split into 3000 configurations8 s
Pass 1: all leaves with ancestor names1.8 s
XML attributes0.5 s
Detection of repeated parent elements0.7 s
Pass 2: ordinals for the repetitions0.9 s
Merge, index and reconciliation2.4 s
totalaround 15 s

The same with JSON and OPENJSON

If the catalog arrives as JSON, there is no detour through pass 1 and pass 2, because an array brings its ordinals with it. The file is loaded as SINGLE_CLOB and split with STRING_SPLIT at the line breaks into one row per configuration. That STRING_SPLIT guarantees no order does not matter here, because every line is a self-contained configuration. After that, a recursive CTE opens every node with OPENJSON: the anchor is the document, the recursive part opens everything that is an object or an array and appends the key or the index to the path. Whatever is neither object nor array is a leaf.

  1: -- --------------------------------------------------------------------------------
  2: -- 147007: The same catalog as JSON Lines - recursively with OPENJSON (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: -- One row per configuration; attributes follow the xmltodict convention (@kind),
  5: -- repeated elements are arrays. Array indexes are counted from 1, as with XML.
  6: DROP TABLE IF EXISTS [stage].[raw_json];
  7: SELECT
  8:    CAST(T02.value AS nvarchar(max)) AS doc
  9: INTO [stage].[raw_json]
 10: FROM
 11:    OPENROWSET(BULK '/tmp/configs.jsonl', SINGLE_CLOB) AS T01
 12:    CROSS APPLY STRING_SPLIT(CAST(T01.BulkColumn AS nvarchar(max)), CHAR(10)) AS T02
 13: WHERE
 14:    LEN(T02.value) > 2;
 15: 
 16: -- --------------------------------------------------------------------------------
 17: -- Open recursively: objects (type 5) and arrays (type 4) are shredded further,
 18: -- everything else is a leaf. OPENJSON returns key/value in Latin1_General_BIN2,
 19: -- hence COLLATE DATABASE_DEFAULT on both sides of the recursion.
 20: -- --------------------------------------------------------------------------------
 21: DROP TABLE IF EXISTS [stage].[leaf_json];
 22: WITH
 23: CTE_node AS
 24: (
 25:    SELECT
 26:        CAST(JSON_VALUE(doc, '$."@id"')  AS nvarchar(20))  COLLATE DATABASE_DEFAULT AS config_id
 27:       ,CAST(JSON_VALUE(doc, '$."@type"') AS nvarchar(20))  COLLATE DATABASE_DEFAULT AS config_type
 28:       ,CAST(doc AS nvarchar(max))                         COLLATE DATABASE_DEFAULT AS node
 29:       ,CAST(N'' AS nvarchar(400))                         COLLATE DATABASE_DEFAULT AS path
 30:       ,CAST(5 AS int)                                                              AS json_type
 31:    FROM
 32:       [stage].[raw_json]
 33:    UNION ALL
 34:    SELECT
 35:        CAST(T01.config_id   AS nvarchar(20))  COLLATE DATABASE_DEFAULT
 36:       ,CAST(T01.config_type AS nvarchar(20))  COLLATE DATABASE_DEFAULT
 37:       ,CAST(T02.value       AS nvarchar(max)) COLLATE DATABASE_DEFAULT
 38:       ,CAST(T01.path
 39:             + CASE WHEN T01.json_type = 4
 40:                    THEN N'[' + CAST(CAST(T02.[key] AS int) + 1 AS nvarchar(10)) + N']'
 41:                    ELSE N'/' + T02.[key] END
 42:             AS nvarchar(400)) COLLATE DATABASE_DEFAULT
 43:       ,CAST(T02.type AS int)
 44:    FROM
 45:       CTE_node T01
 46:       CROSS APPLY OPENJSON(CASE WHEN T01.json_type IN (4, 5) THEN T01.node ELSE N'[]' END) AS T02
 47:    WHERE
 48:       T01.json_type IN (4, 5)
 49: )
 50: SELECT
 51:     config_id
 52:    ,config_type
 53:    ,CAST(path AS varchar(400)) AS path
 54:    ,CAST(node AS varchar(200)) AS value
 55: INTO [stage].[leaf_json]
 56: FROM
 57:    CTE_node
 58: WHERE
 59:    json_type NOT IN (4, 5);
 60: 
 61: SELECT
 62:     COUNT(*)             AS leaves
 63:    ,COUNT(DISTINCT path) AS paths
 64: FROM
 65:    [stage].[leaf_json];
 66: -- 1528476   1026

Two details make the difference between a query that runs and one that does not even compile. First, OPENJSON returns the columns key and value in the collation Latin1_General_BIN2, while the columns of the anchor carry the collation of the database. In a recursive CTE, anchor and recursive part must have identical types, collation included, otherwise SQL Server reports “Types don’t match between the anchor and the recursive part”. The COLLATE DATABASE_DEFAULT on both sides is therefore mandatory. Second, OPENJSON counts array indexes from zero, XML ordinals count from one. The + 1 in the path aligns the two, so that both routes deliver the same paths.

The JSON route needs around 25 seconds for loading and recursion together. It delivers 1,528,476 rows with 1026 paths, that is, 6000 rows and two paths more than the XML route. The difference are @id and @type, which JSON carries as paths of their own, while they are columns on the XML route. All 1,522,476 rows of the XML route reappear with configuration, path and value in the JSON result, the ordinals of the lists included. The code, a single script, is much shorter than the five scripts of the XML route from pass 1 to the merge, but at around 25 seconds the runtime is above the around 15 seconds of the XML route. Whoever has the choice, because the catalog is converted anyway, can skip the XML shredding. What the conversion looks like in Python is shown by the twin of this article, which follows shortly.

Inventory: reading the schema off the data

With the path/value table, the actual question of the project becomes a grouping: which type carries which paths, and how often? Per type and path, the values are counted, as are the configurations in which the path occurs. The share of all configurations of the type then says what one is dealing with.

  1: -- --------------------------------------------------------------------------------
  2: -- 147009: Inventory - which type carries which paths, and how often (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: DROP TABLE IF EXISTS [stage].[inventory];
  5: WITH
  6: CTE_configs AS
  7: (
  8:    SELECT
  9:        config_type
 10:       ,COUNT(DISTINCT config_id) AS configs
 11:    FROM
 12:       [stage].[leaf]
 13:    GROUP BY
 14:       config_type
 15: )
 16: SELECT
 17:     T01.config_type
 18:    ,T01.path
 19:    ,COUNT(*)                                                                         AS n_values
 20:    ,COUNT(DISTINCT T01.config_id)                                                    AS n_configs
 21:    ,CAST(100.0 * COUNT(DISTINCT T01.config_id) / MAX(T02.configs) AS decimal(5, 1)) AS share_pct
 22: INTO [stage].[inventory]
 23: FROM
 24:    [stage].[leaf] T01
 25:    INNER JOIN CTE_configs T02
 26:    ON
 27:      T02.config_type = T01.config_type
 28: GROUP BY
 29:     T01.config_type
 30:    ,T01.path;
 31: 
 32: -- --------------------------------------------------------------------------------
 33: -- Required or optional: from a share of 99 percent a path counts as required for
 34: -- the type, everything below is optional or belongs to a variant.
 35: -- The threshold is deliberately not 100 percent. In real data a required value is
 36: -- missing in a few configurations, and the path should stay required so that
 37: -- exactly these gaps show up. For strict counting use 100.0.
 38: -- --------------------------------------------------------------------------------
 39: SELECT
 40:     config_type
 41:    ,COUNT(*)                                           AS paths
 42:    ,SUM(CASE WHEN share_pct >= 99.0 THEN 1 ELSE 0 END) AS required_paths
 43:    ,SUM(CASE WHEN share_pct <  99.0 THEN 1 ELSE 0 END) AS optional_paths
 44: FROM
 45:    [stage].[inventory]
 46: GROUP BY
 47:    config_type
 48: ORDER BY
 49:    config_type;
 50: -- cargo_bike      640   34   606
 51: -- road_bike       634   29   605
 52: -- trekking_bike   640   29   611
 53: 
 54: SELECT
 55:     path
 56:    ,n_configs
 57:    ,share_pct
 58: FROM
 59:    [stage].[inventory]
 60: WHERE
 61:        config_type = 'trekking_bike'
 62:    AND path LIKE '/Drivetrain/%'
 63: ORDER BY
 64:    path;
 65: -- /Drivetrain/@kind             980   100.0
 66: -- /Drivetrain/Belt              367    37.4
 67: -- /Drivetrain/Chainline         613    62.6
 68: -- /Drivetrain/Chainrings        613    62.6
 69: -- /Drivetrain/CoasterBrake      367    37.4
 70: -- /Drivetrain/FrontDerailleur   613    62.6
 71: -- /Drivetrain/GearRange         367    37.4
 72: -- /Drivetrain/GearRatio_max     613    62.6
 73: -- /Drivetrain/GearRatio_min     613    62.6
 74: -- /Drivetrain/Gears             367    37.4
 75: -- /Drivetrain/HubType           367    37.4
 76: -- /Drivetrain/RearDerailleur    613    62.6
 77: -- /Drivetrain/Sprockets         613    62.6

A path with a share close to one hundred percent is a required attribute of the type. The query draws the line at 99 percent and not at 100, so that a required attribute missing in a few configurations stays required and exactly these gaps show up. The classification is a finding from the data and not a schema statement. Whether an attribute is required in the business sense is decided by the business side, and an element that is present but empty counts as present here. A path with a small share is optional. And a path that occurs in a fixed fraction of the configurations belongs to a variant. The drivetrain of the trekking bike shows this without any prior knowledge: /Drivetrain/@kind appears in all 980 configurations, the chain attributes appear in 62.6 percent of them, the hub attributes in the remaining 37.4 percent. The two variants of the drivetrain can be read off the count, and so can the fact that they exclude each other. The inventory takes under half a second.

The same count is, as a side effect, a data quality tool. A path that occurs in only three out of a thousand configurations is either a rare extra or a typo in the element name. A required attribute at 99.8 percent has two configurations in which it is missing. And a path that counts more values than configurations is either a repetition that pass 2 did not resolve, or a pair of element names in the same configuration that differ only in upper and lower case and collapse under the database’s default collation. This metric, n_values greater than n_configs, is a plausibility check for paths that are assigned more than once within a configuration. In the catalog it applies to no path. Whether the shredding was complete, it does not answer. That is what the reconciliation of the row counts at the end of the merge is for. Whoever wants to derive check rules from such metadata finds the idea continued in Deriving data quality rules from the schema, there for relational schemas.

Generate views instead of writing them

Nobody writes a view with six hundred columns by hand, and nobody should have to. The inventory knows every path per type, and every path becomes a column: MAX(CASE WHEN path = '…' THEN value END) grouped by configuration. STRING_AGG assembles the column list, QUOTENAME turns the path into a valid column name and the type into a valid view name, and sp_executesql runs the DDL. Because paths and type come from the data, they pass through QUOTENAME as identifiers and through a REPLACE that doubles single quotes as literals. With XML names that is mere caution, because they cannot contain single quotes. With keys from JSON it is necessary. List paths with an ordinal stay out. They get views of their own with one row per list element, otherwise columns like accessories_part_3_name would arise that stay empty for anything but four parts.

  1: -- --------------------------------------------------------------------------------
  2: -- 147010: Generate the views from the inventory instead of writing them (SQL Server)
  3: -- --------------------------------------------------------------------------------
  4: -- One view per object type with one column per path. List paths (with [n]) stay
  5: -- out; they get views of their own with one row per list element.
  6: -- Run the script once per object type: first as it stands with 'trekking_bike',
  7: -- then with 'road_bike' and with 'cargo_bike' in @config_type. Every run creates
  8: -- the view of its type (vw_trekking_bike, vw_road_bike, vw_cargo_bike).
  9: -- CREATE VIEW must be alone in its batch, hence the DDL runs through
 10: -- sp_executesql. The typed view and the sample query at the end belong to the
 11: -- trekking bike and assume that its run was the first.
 12: -- Identifiers pass through QUOTENAME, literals through REPLACE('''') - paths from
 13: -- JSON, unlike XML names, may contain single quotes.
 14: DECLARE @config_type varchar(20) = 'trekking_bike';   -- then 'road_bike', then 'cargo_bike'
 15: DECLARE @sql         nvarchar(max);
 16: 
 17: SELECT
 18:    @sql = N'CREATE OR ALTER VIEW [dbo].' + QUOTENAME(N'vw_' + @config_type) + N' AS'
 19:         + N' SELECT config_id'
 20:         + STRING_AGG(
 21:              CAST(N', MAX(CASE WHEN path = ''' + REPLACE(path, '''', '''''') + N''' THEN value END) AS '
 22:                   + QUOTENAME(LOWER(REPLACE(REPLACE(SUBSTRING(path, 2, 400), '/', '_'), '@', 'attr_')))
 23:                   AS nvarchar(max))
 24:             ,N'') WITHIN GROUP (ORDER BY path)
 25:         + N' FROM [stage].[leaf] WHERE config_type = ''' + REPLACE(@config_type, '''', '''''') + N''' GROUP BY config_id;'
 26: FROM
 27:    [stage].[inventory]
 28: WHERE
 29:        config_type = @config_type
 30:    AND path NOT LIKE '%[[]%';
 31: 
 32: EXEC sp_executesql @sql;
 33: 
 34: SELECT
 35:    COUNT(*) AS columns_generated
 36: FROM
 37:    INFORMATION_SCHEMA.COLUMNS
 38: WHERE
 39:    TABLE_NAME = 'vw_' + @config_type;
 40: -- XML route:  trekking_bike 621, road_bike 615, cargo_bike 621
 41: -- JSON route: trekking_bike 623, road_bike 617, cargo_bike 623
 42: GO
 43: -- --------------------------------------------------------------------------------
 44: -- Second layer: typing where it is needed. All values of the generated view are
 45: -- text; only here do price, weight and lumen get their types.
 46: -- --------------------------------------------------------------------------------
 47: CREATE OR ALTER VIEW [dbo].[vw_trekking_bike_typed]
 48: AS
 49: SELECT
 50:     config_id
 51:    ,general_model                                        AS model
 52:    ,TRY_CONVERT(smallint,       general_modelyear)       AS model_year
 53:    ,TRY_CONVERT(decimal(10, 2), general_price)            AS price
 54:    ,TRY_CONVERT(decimal(6, 2),  general_weight)          AS weight_kg
 55:    ,frame_material                                          AS frame_material
 56:    ,drivetrain_attr_kind                                         AS drivetrain_kind
 57:    ,TRY_CONVERT(tinyint,        drivetrain_gears)              AS gears
 58:    ,TRY_CONVERT(smallint,       lighting_front_lumen)            AS lighting_front_lumen
 59: FROM
 60:    [dbo].[vw_trekking_bike];
 61: GO
 62: SELECT
 63:     frame_material
 64:    ,COUNT(*)                        AS n
 65:    ,CAST(AVG(price) AS decimal(10, 0)) AS price_avg
 66: FROM
 67:    [dbo].[vw_trekking_bike_typed]
 68: GROUP BY
 69:    frame_material
 70: ORDER BY
 71:    frame_material;
 72: -- Aluminum   246   3178
 73: -- Carbon     246   3443
 74: -- Steel      248   3426
 75: -- Titanium   240   3116

The view for the trekking bike has 621 columns and comes into being in under half a second. The script creates the view of one type per run. For the road bike and the cargo bike it runs once more with the respective type in @config_type and delivers views with 615 and 621 columns. A query over it, say the average price per frame material over 980 configurations, runs in around 40 milliseconds. This is the moment the approach pays off: a new attribute in the catalog requires no change to shredding, inventory and view generator. It is a new row in the inventory and, on the next run, a new column in the view. Manual work remains only for the typed view, if the attribute is to get a type there.

All values of the generated view are text, just as they stood in the XML. They get types in a second layer, and only the columns that someone needs typed. TRY_CONVERT is the right tool for that, because a single faulty value must not bring the whole view down. What to watch out for per data type is covered in Safe type conversion with T-SQL.

Three limits belong to this. A column name is an identifier of at most 128 characters. For longer input QUOTENAME returns NULL, and because STRING_AGG skips NULL values, the column silently disappears from the view. The column count at the end of the script is therefore more than a control output: 620 paths without ordinal plus config_id must give 621 columns. Furthermore, the mapping of path to column name is not unique. /A/B and /A_B both become a_b, and the view fails with the message that column names must be unique. The same happens with /A/@b and /A/attr_b, which both become a_attr_b, and with Gears and gears as soon as the path column carries a binary collation and the two spellings sit in the inventory as separate paths. Whoever has such names appends a running number from the inventory. SQL Server allows 1024 columns per view, Postgres 1600, where in Postgres a materialized table additionally requires the row to fit into an 8-KB block. With a thousand attributes and one view per type that still works out, with two thousand it no longer does. Then views are generated per assembly instead of per type, that is, one view for the frame, one for the drivetrain, and the consumers join through the configuration ID. And the path/value table itself is an intermediate format, not a target model. It has no types, no constraints, and every query has to pivot first. Whoever uses it as the data model for applications builds the entity-attribute-value pattern with all its well-known drawbacks. As a landing zone for a document with an unknown schema it is exactly right, and the generated views are the layer the consumers see. If a view is queried often, it can be materialized as a table.

Postgres bridge

The same route leads through JSONB in Postgres, and there it is even the fastest. COPY loads the JSON Lines file as one row per configuration, where CSV mode with impossible delimiter and quote characters makes sure that backslashes in the text are not read as escapes. The recursive CTE opens objects with jsonb_each and arrays with jsonb_array_elements including the ordinality, and both happen in one lateral join, so that one recursion serves both cases.

  1: -- --------------------------------------------------------------------------------
  2: -- Load JSON Lines: one row per configuration. CSV mode with impossible quote and
  3: -- delimiter characters keeps backslashes in the text from being read as escapes.
  4: -- --------------------------------------------------------------------------------
  5: DROP TABLE IF EXISTS stage.raw_json;
  6: CREATE TABLE stage.raw_json
  7: (
  8:     doc   jsonb NOT NULL
  9: );
 10: COPY stage.raw_json FROM '/tmp/configs.jsonl' WITH (FORMAT csv, QUOTE E'\x01', DELIMITER E'\x02');
 11: 
 12: -- --------------------------------------------------------------------------------
 13: -- Flatten recursively: objects via jsonb_each, arrays via jsonb_array_elements
 14: -- with ordinality; anything that is neither object nor array is a leaf.
 15: -- --------------------------------------------------------------------------------
 16: DROP TABLE IF EXISTS stage.leaf_json CASCADE;   -- CASCADE: the views of an earlier run depend on it
 17: CREATE TABLE stage.leaf_json AS
 18: WITH RECURSIVE
 19: CTE_node AS
 20: (
 21:    SELECT
 22:        doc ->> '@id'  AS config_id
 23:       ,doc ->> '@type' AS config_type
 24:       ,doc            AS node
 25:       ,''::text       AS path
 26:    FROM
 27:       stage.raw_json
 28:    UNION ALL
 29:    SELECT
 30:        T01.config_id
 31:       ,T01.config_type
 32:       ,T02.node
 33:       ,T01.path || T02.step
 34:    FROM
 35:       CTE_node T01
 36:       CROSS JOIN LATERAL
 37:       (
 38:          SELECT
 39:              '/' || key AS step
 40:             ,value      AS node
 41:          FROM
 42:             jsonb_each(CASE WHEN jsonb_typeof(T01.node) = 'object' THEN T01.node ELSE '{}'::jsonb END)
 43:          UNION ALL
 44:          SELECT
 45:              '[' || ordinality || ']'
 46:             ,value
 47:          FROM
 48:             jsonb_array_elements(CASE WHEN jsonb_typeof(T01.node) = 'array' THEN T01.node ELSE '[]'::jsonb END) WITH ORDINALITY
 49:       ) T02
 50: )
 51: SELECT
 52:     config_id
 53:    ,config_type
 54:    ,path
 55:    ,node #>> '{}' AS value
 56: FROM
 57:    CTE_node
 58: WHERE
 59:    jsonb_typeof(node) NOT IN ('object', 'array');

Loading takes under half a second, the recursion just under four, and the result is row-identical to the OPENJSON route: 1,528,476 rows, 1026 paths. More on COPY and its variants is in Transferring data.

XML directly works in Postgres as well, with xmltable instead of nodes(), and with a trap that is bigger than anything in SQL Server. The function xmltable over the whole 27-MB document with the row path /Catalog/Configuration//*[not(*)] was still running after 36 minutes in the measurement with Postgres 18.6. It reacted neither to pg_cancel_backend nor to pg_terminate_backend and only ended with the container. That was no outlier, as a follow-up measurement with partial catalogs shows: for 50, 100 and 200 configurations the same query takes 4, 17 and 99 seconds, that is, a good five times as long per doubling, and for 3000 configurations that would be hours. The obvious explanation is a call into the XML library libxml2 that does not check for interrupts. What the measurement proves is only the behaviour, not the cause. After the split into one row per configuration, the same xmltable runs as a lateral join over the fragments in around four seconds, and it does so even when it computes the positions right away, because count(preceding-sibling::*) is cheap on a 9-KB fragment. The ordinals only for repeated siblings then come set-based with dense_rank() over configuration, parent path and name. The script for it is in the XML download for Postgres. Postgres generates the views from the paths with format() and runs the DDL with \gexec, three views with around 620 columns each, each in around ten milliseconds.

  1: -- --------------------------------------------------------------------------------
  2: -- Generate the views from the paths: format() builds the DDL, \gexec runs it.
  3: -- List paths (with [n]) and the two attributes of the configuration stay out.
  4: -- --------------------------------------------------------------------------------
  5: SELECT format(
  6:           'CREATE OR REPLACE VIEW stage.vw_%s AS SELECT config_id%s FROM stage.leaf_json WHERE config_type = %L GROUP BY config_id;'
  7:          ,config_type
  8:          ,string_agg(
  9:              format(E'\n   ,max(value) FILTER (WHERE path = %L) AS %I'
 10:                    ,path
 11:                    ,regexp_replace(lower(trim(both '/' from path)), '[^a-z0-9]+', '_', 'g'))
 12:             ,'' ORDER BY path)
 13:          ,config_type)
 14: FROM
 15:    (SELECT DISTINCT config_type, path FROM stage.leaf_json WHERE path NOT LIKE '/@%' AND path NOT LIKE '%[%') T01
 16: GROUP BY
 17:    config_type \gexec
 18: 
 19: SELECT
 20:     table_name
 21:    ,count(*) AS columns_generated
 22: FROM
 23:    information_schema.columns
 24: WHERE
 25:    table_name LIKE 'vw\_%'
 26: GROUP BY
 27:    table_name
 28: ORDER BY
 29:    table_name;
 30: -- vw_cargo_bike      621
 31: -- vw_road_bike       615
 32: -- vw_trekking_bike   621

Limits and decisions

The method has a fixed frame, and that frame should be known before using it.

  • Depth: The ancestor names are columns, and their number is fixed. Four levels cover the catalog, a document with ten levels needs ten columns or a different way of building the path, and the check from pass 1 shows whether the reserve is enough. The recursive CTE of the JSON route does not know this limit, but in SQL Server it knows another one: after 100 recursion levels it stops with an error, until OPTION (MAXRECURSION 0) lifts the limit. Postgres recurses without a limit.
  • Namespaces: local-name() ignores namespaces, which is intended here. Two elements with the same local name from different namespaces thus fall onto one path. Whoever has to tell them apart adds namespace-uri() as a further column. A name() with prefix does not exist in SQL Server’s XQuery, and the prefix would only be a spelling of the URI anyway. To address a namespace in the path on purpose, there is WITH XMLNAMESPACES.
  • Spelling and collation: XML names are case-sensitive, the text columns of the database with the default collation SQL_Latin1_General_CP1_CI_AS are not. Two elements Gears and gears thus collapse onto one path in the inventory, and the detection of repetitions wrongly reports their parent element as repeated. Whoever has such names creates the name and path columns with a binary collation such as Latin1_General_100_BIN2. If both spellings sit in the same configuration, the inventory shows the case through the same metric as a missing ordinal: more values than configurations. If they are spread over different configurations, the metric stays silent, and the case only shows up once the paths are counted under a binary collation.
  • Column widths and character set: Names with varchar(100), values with varchar(200) and paths with varchar(400) are tailored to the catalog, whose longest path has 32 and whose longest value has 19 characters. Longer strings are silently truncated by value() to the target width, without error and without warning. Just as silently, characters that the code page of varchar does not know are lost: Łódź becomes Lódz. Whoever expects such values takes nvarchar. For foreign documents the widths are adjusted to the data. A first pass with wide columns and MAX(LEN(…)) shows what is needed. A blanket varchar(max) is no option for the path: it is the key of the index on the path/value table, and a max type cannot be an index key.
  • Mixed content: Text alongside child elements falls through [not(*)]. Such documents need an additional pass over text() nodes.
  • Elements with XML attributes only: An element like <Warranty years="2"/> has neither text nor children. Pass 1 matches it nonetheless and delivers a row with an empty value, because [not(*)] only asks about child elements. The XML attribute is collected correctly by its own pass as /General/Warranty/@years. The empty row disappears if the predicate is extended by [not(@*) or text()]: leaves without XML attributes are kept even when empty, leaves with XML attributes only if they have text. A plain [text()], by contrast, would also discard deliberately empty elements such as <Belt/>. Whoever reaches for normalize-space() for this gets an error, because the function is missing in SQL Server’s XQuery.
  • Types and localization: All values are text. Decimal point or comma, date formats and yes/no spellings are decided only in the typed view, and there with TRY_CONVERT, not with CONVERT.
  • File size: An xml value holds up to 2 GB, and SINGLE_BLOB reads the whole file at once. With documents in the gigabyte range the split is the bottleneck, and from a certain size a streaming parser outside the database is the better choice. That is the topic of the twin article on Python.
  • Known, small schema: Whoever has twenty attributes and knows them needs none of this. A nodes() with twenty value() columns is then shorter and faster. The generic route pays off when the schema is large, variable or unknown.

As a decision aid:

Starting pointRecommended route
XML, schema large or unknown, file fits into memorySplit, pass 1, detection, pass 2, inventory, generated views
JSON, schema large or unknownOne row per document, recursive CTE with OPENJSON or JSONB, inventory, generated views
XML, but conversion to JSON planned anywayConvert, then the JSON route
Schema small and knownnodes() with fixed value() columns, no path/value table
File in the gigabyte rangeStreaming parser outside the database, load as JSON Lines, rest in SQL

Summary

  • The hours in that project did not come from the programming language but from the design: code per type, access per attribute, schema in your head.
  • The database route does not know the attributes in advance. It loads raw, shreds generically into path and value, and afterwards counts what exists. What it requires are the element of the sub-documents, an upper bound for the depth, and lists in which an element name repeats in at least one configuration.
  • In SQL Server, the split into one row per configuration is the decisive step. After that, one pass over nodes() with the parent axis delivers all leaves in around two seconds.
  • Computing positions for all 1.5 million leaves raises the runtime from around two to around 40 seconds. They are therefore computed only for the elements that repeat according to pass 1.
  • The inventory makes required, optional and variant attributes per type visible, and from it the database generates the views with hundreds of columns itself.
  • JSON does without the second pass, in Postgres JSONB is the fastest route, and xmltable over a whole large document is a trap there.
  • The path/value table is a landing zone, not a data model. The generated and typed views are the layer for the consumers.

FAQ

How do I read XML with an unknown structure in SQL Server?

Load the document as an xml value, split it with nodes() into one row per sub-document, and then fetch every leaf element with nodes('//*[not(*)]'). The parent axis local-name(..) delivers the names of the ancestors, from which the path is built, and value('.') delivers the value. The result is a path/value table from which a grouping makes the schema readable. Required are the element of the sub-documents and an upper bound for the depth of the tree. The method recognises lists only when an element name repeats within them in at least one configuration.

How do repeated elements get their position in SQL Server?

The XQuery expression let $p := . return count(../*[local-name() = "Part"][. << $p]) + 1 counts the same-named siblings that precede the current element in the document, and thus delivers its position. For all 1.5 million leaves this expression is too expensive. It raises the runtime from around two to around 40 seconds. So pass 1 fetches all leaves without position, a grouping reads off at which positions elements repeat at all, and pass 2 computes the position only there. A ROW_NUMBER() over the output of nodes() looks simpler, but does not guarantee the document order.

Isn’t the path/value table exactly the EAV model everyone warns about?

Yes, in form. No, in role. The entity-attribute-value pattern is a problem when it is the permanent data model of an application, because types, constraints and simple queries are missing. Here the table is an intermediate stage between the raw document and the generated views. It is filled once per load run, counted once, and the consumers see views with real columns. Whoever queries the views often materializes them and is back at an ordinary table model.

XML or JSON: what should I load if I have the choice?

JSON, if the ordinals of lists matter and the conversion happens anyway. An array brings its order with it, and the recursive CTE needs no second pass. XML directly, if the file arrives that way and should not be touched again. In SQL Server the XML route took around 15 and the JSON route around 25 seconds for 1.5 million values. In Postgres, JSONB was clearly ahead at around four seconds.

Why not OPENXML?

OPENXML with sp_xml_preparedocument delivers an edge table of the whole document in which every node is a row with an ID and a parent ID, in document order. That sounds like exactly the generic access this article is looking for. In the measurement, the edge table alone for 3 million nodes took around 55 seconds, the text column arrives as ntext and cannot be indexed, and the prepared document occupies memory until it is explicitly released. The route through nodes() was around thirty times faster in this measurement and needs no handles.

Why not just nodes() with fixed paths per attribute?

Because that is exactly the loop in the code, only in SQL: one value() call per attribute, a thousand calls per type, and a change for every new attribute. For a known schema with twenty attributes that is the right way. For a thousand attributes that nobody knows completely, the generic shredding with a subsequent inventory in SQL is the version that does not have to change with every catalog change.

Upstream:

Downstream:

Downloads: