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:

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.