EditIntro
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.
EditExtract 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 NamesEditXML 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