How to script XML Schema collection in SQL Server 2012?

Question

I need to make script of XML schema collections in my Database and then insert each xml inside a table field as string.

I need to do this to compare current schema collections with the new schema collections after it is regenerated.

I have no idea where to start!

Answer 1

If I understand the Question correctly, you need to use the XML_SCHEMA_NAMESPACE built-in function to extract the Schema Collection, and you can find the list of those in the sys.xml_schema_collections system catalog view. You can use something along the lines of:

SELECT CONVERT(NVARCHAR(MAX), XML_SCHEMA_NAMESPACE(sch.[name], xsc.[name])) AS [XSD]
FROM   sys.xml_schema_collections xsc
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = xsc.[schema_id]
WHERE  xsc.[xml_collection_id] <> 1;

I put the WHERE xsc.[xml_collection_id] <> 1 filter in there to avoid getting the following error:

Msg 6314, Level 16, State 1, Line 1
Collection specified does not exist in metadata : 'sys'