Find all XML data columns in SQL Server database

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


select schema_name(t.schema_id) + '.' + as [table],
       c.column_id, as column_name,
       type_name(user_type_id) as data_type,
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],


  • 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


  • 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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.