XML in SQL Server: Reading, Processing & Optimizing

Two Ways to Work with XML

SQL Server gives you two fundamentally different approaches for handling XML: the legacy OPENXML rowset function, and the native xml data type with XQuery/XPath methods (.query(), .value(), .nodes(), .exist(), .modify()). Almost all new code should use the xml data type; OPENXML exists mainly for backward compatibility with pre-2005 code.

OPENXML (Legacy)

DECLARE @doc INT, @xml XML = N'<Root><Row ID="1" Name="A" /><Row ID="2" Name="B" /></Root>';

EXEC sp_xml_preparedocument @doc OUTPUT, @xml;

SELECT ID, Name
FROM OPENXML(@doc, '/Root/Row', 1)
WITH (ID INT '@ID', Name NVARCHAR(50) '@Name');

EXEC sp_xml_removedocument @doc;

OPENXML requires an explicit sp_xml_preparedocument call, which parses the entire document into an internal tree in the MSDB internal memory cache; and if you forget sp_xml_removedocument, that memory stays pinned for the session. This is the single most common cause of memory leaks in older XML-processing procedures.

The xml Data Type (Recommended)

DECLARE @xml XML = N'<Root><Row ID="1" Name="A" /><Row ID="2" Name="B" /></Root>';

SELECT
    r.value('@ID', 'INT')          AS ID,
    r.value('@Name', 'NVARCHAR(50)') AS Name
FROM @xml.nodes('/Root/Row') AS t(r);

No prepare/remove pair, no manual memory management, and the query optimizer can push predicates into the XML processing when a suitable index exists (more on that below).

Shredding XML into Relational Rows

.nodes() is the workhorse for turning XML into a rowset you can join against normal tables. A common pattern is passing a batch of IDs or a small document as a stored procedure parameter and shredding it directly in the query:

DECLARE @ids XML = N'<ids><id>100</id><id>101</id><id>102</id></ids>';

SELECT o.OrderID, o.CustomerID, o.OrderDate
FROM Orders o
INNER JOIN (
    SELECT t.c.value('.', 'INT') AS ID
    FROM @ids.nodes('/ids/id') AS t(c)
) AS parsed ON o.OrderID = parsed.ID;

This pattern replaces the older habit of building a comma-separated string and splitting it: XML shredding is faster, type-safe, and avoids string-splitting edge cases entirely. If you're on SQL Server 2016 or later, also consider passing a JSON array and using OPENJSON instead; for pure ID lists JSON tends to parse a little faster and is easier to construct from application code.

Reading Values: .value() vs. .query()

Method	Returns	Use for
`.value()`	Scalar SQL value	Extracting a single attribute/element into a typed column
`.query()`	XML fragment	Extracting a sub-tree, keeping it as XML
`.nodes()`	Rowset	Shredding repeating elements into rows
`.exist()`	0/1/NULL	Filtering rows in a WHERE clause
`.modify()`	(mutates in place)	Inserting/updating/deleting nodes with XML DML

A frequent mistake is using .value() in a loop over many rows without an XML index: each call re-parses the document. If you're extracting the same value from the same XML column across thousands of rows, that cost adds up fast.

XML Indexes

Without an index, SQL Server has to shred the entire XML blob on every query that touches it; there is no way to "seek" into unindexed XML. For any table where XML columns are queried regularly, create a primary XML index, then secondary indexes matching your access pattern:

-- Primary XML index (required before any secondary index)
CREATE PRIMARY XML INDEX PXML_Orders_Payload
    ON dbo.Orders(Payload);

-- Secondary index optimized for .value() lookups
CREATE XML INDEX SXML_Orders_Payload_Path
    ON dbo.Orders(Payload)
    USING XML INDEX PXML_Orders_Payload FOR PATH;

-- Secondary index optimized for .nodes()/.exist() with VALUE-style filters
CREATE XML INDEX SXML_Orders_Payload_Value
    ON dbo.Orders(Payload)
    USING XML INDEX PXML_Orders_Payload FOR VALUE;

-- Secondary index optimized for .query() returning sub-trees
CREATE XML INDEX SXML_Orders_Payload_Property
    ON dbo.Orders(Payload)
    USING XML INDEX PXML_Orders_Payload FOR PROPERTY;

The primary XML index alone is often the biggest win: it stores a shredded, relational representation of every node, attribute, and value internally, so path-based lookups don't require re-parsing. It also isn't free: it typically adds significant storage overhead (often larger than the source column itself) and slows down inserts/updates on that column, so only index XML columns that are actually queried, not just stored.

Common Performance Traps

Forgetting sp_xml_removedocument: With OPENXML, this leaks memory for the lifetime of the session; always wrap it in a TRY/FINALLY-style cleanup.
Untyped .value() casts: .value('@ID', 'VARCHAR(MAX)') instead of a proper narrow type forces string comparisons downstream and blocks index usage.
Re-parsing large XML repeatedly: If a stored procedure calls .value() or .query() multiple times against the same variable, SQL Server does not cache the parse between calls within the same statement in all cases; restructure to shred once with .nodes() and reuse the rowset.
Storing huge XML in wide tables without a separate table: Large XML blobs bloat page reads for every query against the row, even ones that never touch the XML column. Move large XML into a separate 1:1 table.
Using XML where relational columns would do: If the "XML" is really just 3–4 fixed fields per row, shredding it into real columns at write time is almost always cheaper long-term than repeatedly querying XML at read time.

Writing and Modifying XML

FOR XML generates XML from relational data; .modify() with XML DML mutates XML in place without rewriting the whole document:

-- Generate XML from a query
SELECT CustomerID, OrderDate
FROM Orders
WHERE CustomerID = 42
FOR XML PATH('Order'), ROOT('Orders');

-- Insert a node into an existing XML column
UPDATE dbo.Orders
SET Payload.modify('
    insert <Status>Shipped</Status>
    as last into (/Order)[1]
')
WHERE OrderID = 1001;

.modify() is far cheaper than reading the whole XML value into the application, editing it, and writing it back, especially for large documents, since it avoids round-tripping the entire blob.

The Bottom Line

Use the xml data type and XQuery methods for new development; reserve OPENXML for legacy code you can't yet refactor. Index any XML column that's queried with any regularity, and be deliberate about which secondary index type matches your access pattern. And before reaching for XML at all, ask whether the data is genuinely hierarchical/variable-shape: if it's really just a fixed set of fields, plain relational columns will outperform XML every time.

← Back to Blog