Getting the schema for an XML column in SQL Server 2005
Posted: Tuesday, September 06, 2005 8:27 AM
by
bruce
Filed under: Home, Tips, VS2005, Newsletter
There is no question that the XML column data type in SQL Server 2005 opens up a large number of interesting possibilities. Some that I’ve even been lucky enough to work on myself. In this particular instance, I needed to find out the schema against which a typed XML document will be validated. The idea is to provide some client-side validation for the XML before submitting it to the database server. This doesn’t prevent SQL Server from redoing the validation (there isn’t any way to prevent that from happening that I’ve found), but it does reduce network traffic in the case of bad data.
The problem I faced was that the schema information is not readily apparent. After some digging, the I found that the following SQL will get the string representation of the schema.
SELECT Object_Name(Object_id) [TableName], col.name, s.name, XML_SCHEMA_NAMESPACE(sch.name,s.name)
FROM (sys.columns col INNER JOIN sys.xml_schema_collections s ON col.xml_collection_id = s.xml_collection_id)
JOIN sys.schemas sch on s.schema_id = sch.schema_id
WHERE Object_Name(Object_id) = @TableName
AND col.name = @ColumnName
The first two columns are the table and column name. The third column is the name of the schema used for the column. This is the name as found in XML_SCHEMA_COLLECTIONS. The fourth and final column contains the string representation of the schema.
If you would like to receive an email when updates are made to this post, please register here