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/Gearswith the value14. 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.
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.
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 section below.
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 <...
