liveBook · Manning

Sp_xml_preparedocument

As explained in 100 SQL Server Mistakes and How to Avoid Them

sp_xml_preparedocument is a SQL Server system stored procedure that parses XML text and creates an in-memory document representation identified by a handle.

Mistake: treating OPENXML() as a self-contained parser. sp_xml_preparedocument parses XML through an MSXML parser and returns a handle to an in-memory node tree that OPENXML() can consume. section 3.4.1

The cleanup mistake

A shredding routine can prepare a document, pass its handle to OPENXML(), and forget cleanup. Always call sp_xml_removedocument afterward so the in-memory document does not continue consuming memory. section 3.4.1

Use the procedure as part of a complete XML-shredding sequence:

  • Prepare the XML document before passing its handle to OPENXML(). section 3.4.1
  • Remove the prepared document after OPENXML() finishes. section 3.4.1

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains parsing, handle creation, memory cleanup, and surrounding OPENXML() flow, but not parser internals.
Examples
Uses employee contracts, skills, XML hierarchy, and an OPENXML() shredding script.
What it assumes
Assumes XML and OPENXML() familiarity while identifying parsing, XPath navigation, and cleanup requirements.
What you can do after
Apply it via T-SQL to prepare XML handles for OPENXML() and release them after shredding.
How it connects
Connects XML parsing with OPENXML(), sp_xml_removedocument, XPath, and relational data shredding.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.