The query below lists all columns with XML data types in SQL Server database.

Query

select schema_name(t.schema_id) + '.' + t.name as [table],
       c.column_id,
       c.name as column_name,
       type_name(user_type_id) as data_type,
       is_xml_document
from sys.columns c
join sys.tables t
     on t.object_id = c.object_id
where type_name(user_type_id) in ('xml')
order by [table],
         c.column_id;

Columns

• table - name of the table with schema name
• column_id - column position in table
• column_name - name of the column
• data_type - type of data
• is_xml_dcoument -
  • 1 - content is a complete XML document with only one root element
  • 0 - content is a document fragment

Rows

• One row represents one column with a XML data type
• Scope of rows: all columns containing XML data types in the database
• Ordered by schema name, table name and column position in table

Sample results