SQL SERVER the best implementation policy catalog view of XML

Catalog views for native XML support

• The purpose of the catalog view is to provide metadata information about the usage of XML. Several of these catalog views are discussed below.

XML Index

The XML index entry appears in the catalog view sys.indexes, and the index "type" is 3. The "Name" column contains the name of the XML index.

XML indexes are also recorded in catalog view sys.xml_indexes, which contains all the columns of sys.indexes and some special columns that make sense for XML indexes. The value NULL in the column "Secondary_type" represents the primary XML index, and the value "P", "R", and "V" represent the PATH, property, and value secondary XML indexes respectively.

The spatial utilization of an XML index can be found in the table-valued function sys.fn_indexinfo (). This function provides a lot of information, such as the number of disk pages consumed, the average row size (bytes), the record count, and other information for all index types, including XML indexes. This information is provided for each database partition, and the XML index uses the same partitioning scheme and partition function of the base table.

Example: Spatial utilization of XML indexes

SELECT sum(Pages)
FROM　　sys.fn_indexinfo ('T', 'idx_xCol_Path' , DEFAULT, 'DETAILED')

This results in the number of disk pages that the XML index in table T Idx_xcol_path occupies in all partitions. If you do not use the sum () function, the result returns disk page utilization for each partition.

Retrieving XML schema Collections

The XML schema collection is enumerated in the catalog view sys.xml_schema_collections. The XML schema collection "SYS" is defined by the system and contains predefined namespaces that can be used in all user-defined XML schema collections without explicit loading. The list contains namespaces for XML, XS, XSI, FN, and xdt. The other two noteworthy catalog views are: Sys.xml_schema_namespaces, which enumerates all the namespaces in each XML schema collection; sys.xml_components, which enumerates all the XML schema components in each XML schema.

The built-in function xml_schema_namespace (SchemaName, XmlSchemaCollectionName, Namespace-uri) produces an instance of an XML data type that contains XML An XML schema fragment that contains the schema (except for the predefined XML Schema) contained in the schema collection.

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

• Write a T-SQL query on the appropriate directory view of the XML schema collection.

• Use built-in functions xml_schema_namespace (). You can apply the XML data type method on the output of this function. However, you cannot modify the underlying XML schema.

• The following examples illustrate these concepts.

Example: Enumerating XML Namespaces in an XML schema collection

Use the following query for the XML schema collection "mycollection":

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: Enumerating the contents of an XML schema collection

The following statement enumerates the contents of the XML schema collection "mycollection" in relational schema dbo.

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

By specifying the target namespace as the third parameter of Xml_schema_namespace (), you can get a separate XML schema from the collection as an instance of an XML data type, as shown below.

Example: Output the specified schema in the XML schema collection

The following statement outputs an XML schema that has a target namespace of "Http://www.microsoft.com/books" from the XML schema collection "MyCollection" in the relational schema dbo.

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

Querying XML schemas

If you need to query an XML schema that has been loaded into an XML schema collection, you can use the following methods:

• Write a T-SQL query on the catalog view of the XML schema namespace.

• Create a table containing XML data type columns to store XML schemas, in addition to loading XML schemas into an XML type system. You can use the XML data type method to query an XML column. Also, you can generate an XML index on the column. However, it is up to the application to maintain consistency between the XML schemas stored in the XML columns and the XML schemas stored in the XML type system. For example, if you delete an XML Schema namespace from an XML type system, you must also remove the namespace from the table to maintain consistency.