SQL Server XML Tricks

Edit

Intro

Here you will find a list of "tricks" and sample code for working with the XML data type in SQL Server 2005+. These topics are driven by audience member questions during my SQL/XML presentations.

Edit

Extract Element Names

An audience member wanted to query the XML data stored in a table in order to compare the list of elements from the XML in one row to another (presumably to detect if the untyped XML's "schema" changed from one row to another). Here's a way to generate a SQL table structure containing the namespace, local names, and parent's name for each element found in the table's XML. Once in this flat form, traditional SQL querying can be used to detect the changes.

Extract Element Names

Edit

XML Indexes

This article was prompted by a question from the audience about the internals of XML Indexes. In particular, they were interested in where the internal system table used by Primary XML Indexes is stored, and what might happen to this index if the database is detached and then reattached on another server.

XML Indexes