Schema visibility using xml_schema_namespace

One of the issues I’ve faced is the constant referencing and rereferencing of XML Schemas that are stored against a database.  the xml_schema_namespace dmv helps with this.  it takes 2 parameters.  the schema of the database object XML Schema and Name of the XML Schema (and as I mentioned Schema too many times here is an example).

xml_schema_namespace(N'dbo','XMLSchema')

This returns a list of the contents of the schema, similar results can be returned by scripting CREATE AS … on the XML schema but this command returns purely the XML schema with out CREATE XML SCHEMA AS …. and can the query can be used in the snippet manager of your choice.

I extended this capability for my own purposes, using a cursor to retrieve each instance of an xml schema in the database (note: db specific command not server) and return it in one large xml blob. This is part of a longer term project that i am working on but i thought it might be useful to blog about this specific part of it.

--------------------------------------------------
--Author: Phil Quinn
--Date: 5th October 2012
--Contact: Twitter @SQLPippy
-- Email: Philip.quinn@me.com
-- http:\\rqdb.wordpress.com
--
--------------------------------------------------

DECLARE @AllSchemas varchar(max) =”
DECLARE @SchemaNameVar varchar(50)
DECLARE @XMLSchemaNameVar varchar(100)

DECLARE schema_cursor CURSOR FOR
SELECT s.name AS SchemaName
,xs.name AS XMLSchemaName
FROM sys.xml_schema_collections XS
INNER JOIN sys.schemas S ON s.schema_id = XS.schema_id
WHERE s.name <> ‘sys’
–get a list of all xml schema’s and including DB Schema name

OPEN schema_cursor

FETCH NEXT FROM schema_cursor
INTO @SchemaNameVar, @XMLSchemaNameVar

WHILE @@FETCH_STATUS = 0
BEGIN
–use @AllSchemas as a varchar as the xml var does not have a + operator
–could use and insert variable but that will be version 2 🙂
SET @AllSchemas=@AllSchemas + CAST(xml_schema_namespace(@SchemaNameVar,@XMLSchemaNameVar) AS VARCHAR(max))
— Get the next Schema.
FETCH NEXT FROM schema_cursor
INTO @SchemaNameVar, @XMLSchemaNameVar
END
CLOSE schema_cursor;
DEALLOCATE schema_cursor;
–Close and deallocate cursor

–Transfer back from a varchar to a XML variable
SELECT CAST(@AllSchemas AS XML)

Advertisements
This entry was posted in SQL, XML, XML Schemas. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s