liveBook · Manning

OPENXML

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

OPENXML is a SQL Server table-valued function that exposes rows and columns from an XML document or parsed XML representation.

OPENXML is a SQL Server table-valued function for shredding XML into relational rows and columns. It suits hierarchical employee, contract, and skill data, but requires parsed XML and careful cleanup. section 3.4.1

Mistake 1: treating OPENXML as a complete XML parser. A safer workflow is:

  • Store or receive the XML document. section 3.4.1
  • Call sp_xml_preparedocument to create an in-memory parsed representation and handle. section 3.4.1
  • Pass the handle, an XPath row pattern, and mappings to OPENXML; use ../ to reach parent levels. section 3.4.1
  • Call sp_xml_removedocument immediately afterward to release consumed memory. section 3.4.1

Employee contracts and skills

An EmployeeContracts document can contain employees, repeated contracts, and repeated skills. OPENXML can flatten this hierarchy into employee, contract, and skill columns for relational storage.

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

How deep it goes
Explains parser preparation, XPath row selection, parent traversal, spillover options, and mandatory memory cleanup.
Examples
Uses nested employee contracts and skills, relational target tables, and a T-SQL shredding table variable.
What it assumes
Assumes basic XML and relational-table familiarity while defining XPath navigation and document preparation requirements.
What you can do after
Operate OPENXML by preparing documents, selecting XPath rows, mapping columns, and releasing parsed-document memory.
How it connects
Connects OPENXML with XML, XPath, MSXML, sp_xml_preparedocument, sp_xml_removedocument, XQuery, nodes(), and value().
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.