liveBook · Manning

XML parser

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

An XML parser is software that reads XML text and constructs a structured representation of its elements, attributes, and content.

In SQL Server, OPENXML() does not parse XML itself. Prepare the document with sp_xml_preparedocument, which returns a handle to an in-memory tree of its nodes. section 3.4.1

Mistake: prepare the document and forget cleanup. Call sp_xml_removedocument after OPENXML() to release the memory consumed by the parsed representation. section 3.4.1

Employee contract data

An EmployeeContracts document nests employees, contracts, and repeating skills. OPENXML() can shred these levels into relational contract and skill data, using XPath to select rows and ../ to reach parent nodes. section 3.4.1

Apply the parser workflow carefully:

  • Prepare the XML, pass its handle to OPENXML(), then remove the document. section 3.4.1

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

How deep it goes
Explains parser handles and in-memory node trees, but stops short of parser internals.
Examples
Uses nested employee contracts containing repeated skills as the concrete shredding example.
What it assumes
Assumes XML and relational targets while introducing XPath navigation and parser preparation for OPENXML().
What you can do after
Operate SQL Server XML parsing with preparation handles, OPENXML(), XPath mappings, and explicit memory cleanup.
How it connects
Connects XML parsing with OPENXML(), XPath, XQuery, nodes(), value(), and relational 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.