Select XML Schema Collection type of an XML Column

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'

No comments: