Catalog Views for XML

XML Indexes

XML index entries appear in the catalog view, sys.indexes, with the index "type" 3. The name column contains the name of the XML index.

XML indexes are also recorded in the catalog view, sys.xml_indexes. This contains all the columns of sys.indexes and some specific ones that are useful for XML indexes. The value NULL in the column, secondary_type, indicates a primary XML index; the values 'P', 'R' and 'V' stand for PATH, PROPERTY, and VALUE secondary XML indexes, respectively.

The space use of XML indexes can be found in the table-valued function sys.dm_db_index_physical_stats. It provides information, such as the number of disk pages occupied, average row size in bytes, and number of records, for all index types.. This also includes XML indexes. This information is available for each database partition. XML indexes use the same partitioning scheme and partitioning function of the base table.

Retrieving XML Schema Collections

XML schema collections are enumerated in the catalog view, sys.xml_schema_collections. The XML schema collection "sys" is defined by the system. It contains the predefined namespaces that can be used in all user-defined XML schema collections without having to load them explicitly. This list contains the namespaces for xml, xs, xsi, fn, and xdt. Two other catalog views are sys.xml_schema_namespaces, which enumerates all namespaces within each XML schema collection, and sys.xml_components, which enumerates all XML schema components within each XML schema.

The built-in function XML_SCHEMA_NAMESPACE, schemaName, XmlSchemacollectionName, namespace-uri, yields an xml data type instance.. This instance contains XML schema fragments for schemas that are contained in an XML schema collection, except the predefined XML schemas.

You can enumerate the contents of an XML schema collection in the following ways:

• Write Transact-SQL queries on the appropriate catalog views for XML schema collections.

• Use the built-in function XML_SCHEMA_NAMESPACE(). You can apply xml data type methods on the output of this function. However, you cannot modify the underlying XML schemas.

These are illustrated in the following examples.

Example: Enumerate the XML Namespaces in an XML Schema Collection

SELECT XSN.name
FROM    sys.xml_schema_collections XSC JOIN sys.xml_schema_namespaces XSN
    ON (XSC.xml_collection_id = XSN.xml_collection_id)
WHERE    XSC.name = 'myCollection'   

Example: Enumerate the Contents of an XML Schema Collection

SELECT XML_SCHEMA_NAMESPACE (N'dbo', N'myCollection')

Example: Output a Specified Schema from an XML Schema Collection

SELECT XML_SCHEMA_NAMESPACE (N'dbo', N'myCollection', 
N'https://www.microsoft.com/books')

Querying XML Schemas

You can query XML schemas that you have loaded into XML schema collections in the following ways:

• Write Transact-SQL queries on catalog views for XML schema namespaces.

• Create a table that contains an xml data type column to store your XML schemas and also load them into the XML type system. You can query the XML column by using the xml data type methods. Also, you can build an XML index on this column. However, with this approach, the application must maintain consistency between the XML schemas stored in the XML column and the XML type system. For example, if you drop the XML schema namespace from the XML type system, you also have to drop it from the table in order to preserve consistency.

See Also

Other Resources