I spent an afternoon looking for a way to get the XML Schema Collection type of a given column.
I wanted to copy the contents of a table (that has an XML column with a user defines Schema Collection) to another table but I get this error message: Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query.
I understand that I have to CONVERT it to the same XML Schema Collection but how do I do so dynamically?
I ended up resorting to MSDN's SQL forums. The next morning, Peso, an MVP, gives me the answer:
SELECT object_name(ac.object_id) AS TableName,
ac.name as ColumnName,
x.name AS CollectionName
FROM sys.all_columns AS ac
LEFT JOIN sys.xml_schema_collections AS x ON x.xml_collection_id = ac.xml_collection_id
WHERE object_name(ac.object_id) = 'MyTableNameHere'