Catalog Views and XML - SQL Server 2008

Catalog views are views that wrap information stored in system tables. The XML datatype is represented in these views. For example, you can retrieve all the XML schemas registered in your database instance using the sys.xml_schema_collections view. You can retrieve elements and attributes that are registered by your schemas using the sys.xml_schema_elements and sys.xml_schema_attributes views.

The following code sample uses the dynamic management views to look at all the namespaces in a database instance, all the elements and attributes for a particular namespace, and also any indexes on XML columns:

SELECT * FROM sys.xml_schema_collections
SELECT * FROM sys.xml_schema_elements
SELECT * FROM sys.xml_schema_attributes
SELECT * FROM sys.xml_schema_namespaces
SELECT * FROM sys.xml_indexes

Interesting scenarios for using these views occur when you want to figure out what namespaces exist in your server, what indexes you’ve created for your different XML types, and what the actual XML looks like across your server using the elements and attributes views. The following example uses the dynamic management views to enumerate all namespaces in your XML schema collections on the server. The code joins together the schema collection and schema namespace views so that you can see the name of your schema namespace. Without this join, if you query the sys.xml_schema_collections view, you would see only the name of the namespace you defined, which may be different from the name of the namespace in your schema.

SELECT *
FROM sys.xml_schema_collections xmlSchemaCollection
JOIN sys.xml_schema_namespaces xmlSchemaName
ON (xmlSchemaName.xml_collection_id = xmlSchemaName.xml_collection_id)
WHERE xmlSchemaCollection.name = 'Customer'
go

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics