How to script XML Schema collection in SQL Server 2012?

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!

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'

StackExchange Database Administrators Q#141297, answer score: 3