Should You Store XML in a Database Column or Shred It

Whether to keep XML in a database column, or to cut it into tables, is a decision people make once, early, and live with for years. The common failure is to pick the column because it is easy to load, and to discover later that every question needs a re-parse of every document. The opposite failure is to shred everything into tables and then be unable to give back the document a partner sent. This article lays out the options, what each engine does to your documents, and what the choice costs on 200,000 invented orders.

The options

There are four ways to hold an XML document, and the choices are not exclusive.

1. Text or LOB column (text, CLOB, varchar(max), BLOB). The database stores bytes and knows nothing about them.

2. Native XML column (xml, XMLTYPE). The database parses on the way in, rejects malformed input, and gives you XPath functions.

3. Shredded tables. The fields become rows and columns in the ordinary relational model.

4. Hybrid. The raw document in one column (text or native), plus extracted columns that you index and query.

A fifth option sits outside the database: keep the files in object storage and the database holds a key, a checksum and the extracted fields. For large documents it is the most common arrangement in production.

What the engine does to your document

The first question is whether you need the same document back. Native XML types parse, and a parsed document is not your text.

PostgreSQL xml      keeps comments, whitespace, spacing inside tags; drops the XML declaration
SQL Server xml      drops whitespace-only text between elements, collapses spaces inside tags, keeps comments;
                    rejects an encoding declaration in an nvarchar literal (Msg 9402)
Oracle XMLType      keeps the declaration; returns a single-line document indented
text / CLOB / BLOB  keeps every byte (BLOB also keeps the encoding)

From the same sample (<a x="1" y = "2"><!-- c --><b/> followed by a newline and <c> t </c></a>), SQL Server returned <a x="1" y="2"><!-- c --><b/><c> t </c></a>. If a digital signature, a checksum or a legal requirement depends on the bytes, a native type is the wrong primary store, and a BLOB or a file is the right one. Canonical XML is the way to compare documents that differ only in such details (the c14n call in parsing and writing XML in Python does that).

What the engine checks

A native type checks syntax, and the engines differ even there:

                      'not xml'      two roots           malformed
PostgreSQL (content)  accepted       accepted            error with line
SQL Server            accepted       accepted            Msg 9436
Oracle                ORA-31011      ORA-31011           ORA-31011
MySQL (text column)   accepted       accepted            nothing checks

(PostgreSQL's document mode and a check (xml_is_well_formed_document(...)) constraint tighten it.) None validates against a schema unless you ask: SQL Server has typed XML with a schema collection, Oracle has registered schemas, and in PostgreSQL and MySQL validation lives in the application. The gap between well-formed and valid is the one in well formed versus valid XML, and a database column that only checks syntax lets a document with the wrong structure in without complaint. Validate on the way in, with the real schema, in the loader.

What the choice costs

For 200,000 documents of 160 bytes on average:

text, one document per row (MySQL)                         26.5 MB
PostgreSQL xml column                                        40 MB     (raw text: 30 MB)
SQL Server xml column (internal binary form)                 47 MB     (table 53 MB)
Oracle XMLType, default binary                               41 MB
Oracle XMLType, store as securefile binary xml               28 MB
PostgreSQL shredded: orders 10 MB + items 19 MB              29 MB

The native types are 1.3 to 1.6 times the text. The shredded form, with 200,000 order rows and 399,998 item rows, was smaller than the native column, and it contains the parsed values with their types. Small documents carry large per-row overhead everywhere, so the ratios move with the document size.

Query cost is the other side of it. A filter on one field of the native column parsed every document:

PostgreSQL xpath scan                439 ms        generated column + index:         9.5 ms
SQL Server value() scan              287 ms        persisted computed column:        4 ms
Oracle xmlexists scan               1.13 s         function-based index:           0.04 s

and the cost of getting to the shredded form was a one-time pass:

PostgreSQL xmltable → orders (200,000 rows)     2.2 s
PostgreSQL xmltable → items  (399,998 rows)     3.6 s

I checked the shredded counts against an independent one (sum(count(/order/items/item)) over the documents was 399,998, the same as the item table). The details of the statements are in using xmltable in PostgreSQL to turn XML into rows, and the other engines have their equivalents (shredding XML with the nodes and value methods in SQL Server, Oracle XMLTable and XMLType basics).

A decision procedure

Ask five questions, in this order.

Do you have to return the original? If yes, keep the bytes: a text or LOB column, or object storage with a checksum. A native type may still sit beside it for querying.

Do you query inside the documents? If never (archive, audit trail, pass-through), a text column is enough and the cheapest. If sometimes, a native column plus an index on the extracted field. If on every request, shred.

Is the structure stable? A format with a schema that changes once a year can be shredded and migrated like any other table. A format whose elements vary by customer or by version, and where the variations are the point, resists tables; keep the documents and extract what you can.

How much data, and how often does it change? Millions of documents queried by two fields: shred or promote those fields. A few thousand documents queried rarely: store and scan.

Who reads it? Analysts want tables. Integration code wants the document. Provide both if you must, and make one the source of truth and the other derived, never edited by hand.

Most answers end at the hybrid: the raw document stored once, and the columns you filter on extracted at load time and indexed. It is the form that survives requirements changes, because you can add a column by reprocessing the stored documents. Its price is a copy of the extracted fields and a loader that fills them consistently. Test that consistency in the loader: parse, extract, store, and reconcile counts.

Mistakes in both directions

A native column used as a dump. Everything goes in, and every report parses every document. The scan time grows linearly, the first slow report becomes a project, and the fix is the migration you postponed.

Over-shredding. Forty tables for a document with forty element types, joins everywhere, and no way back to the document. If the shape is a tree of mostly optional parts, the tree is the right representation.

Treating the database as the validator. Syntax checks are not schema checks. Put the schema in the pipeline and fail files at the door.

Forgetting the encoding. A document with encoding="ISO-8859-1" stored as text in a UTF-8 column is wrong at some point, and the declaration inside it will lie to the next reader. Normalize on load and rewrite or drop the declaration.

The overview of the mechanics across the engines is storing and querying XML inside a database, and the cost side is indexing XML columns and what it costs. The standards behind the SQL/XML functions are described in the PostgreSQL XML type documentation, and SQL Server's guidance on the question is in XML data (SQL Server).

When the answer is neither a column nor tables

The documents are large files. Hundreds of megabytes per file belong in object storage, with the database holding metadata. Parsing them whole inside the database wastes memory.

The data is really JSON in disguise. If the consumers speak JSON and the XML is a legacy format on the way out, convert once at the edge and store JSON (XML versus JSON for data exchange).

Search is the goal. If users search the text of documents, a search index is built for it, and a relational column is not.

You do not need to keep it. If the XML is a transport and the fields are the product, load the fields, keep the file for the retention period, and delete both when it ends.