Menu

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

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

DEV Community: database·Marcus·18 days ago
#yudrBkbn
#devtodatabase#devto#dev#path#configuration
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.