views:

205

answers:

2

I have an SQL 2005 database and I know that in the database there is a table which has got some xml strings in it. How can I find this table(s)?

+1  A: 

If the fields are actually of type XML, then this query will give you what you're looking for:

select * from information_schema.columns
where DATA_TYPE = 'XML'

Marc

marc_s
The field is a varchar. Thats what makes things a bit difficult
Lonzo
+2  A: 

Run this:

select 'select distinct ''' || a.name || '.' || b.name 
|| '''  from ' || b.name 
|| 'where ' || b.name || ' like ''%<%/>%'' union ' 
from systable a 
join syscolumns b on (a.id = b.id)
join systypes c on (b.type = c.xtype) 
where a.type ='U' and c.name = ('CHAR', 'CHARN', 'VARCHAR', 'VARCHARN');

The first result set will have one row per character column in the database:

select distinct 'table.column' from table where column like '%<%/>%' union

Take that resultset, snip off the last union, and run the resultset as a SQL statement. It'll bring back the table name and column name for any column that has one or more rows that look XML-ish.

Edit: this is from memory; the join to systypes and the type names may be wrong, so select from systypes and check.

tpdi
Good sultion. Couple of remarks: you'll need '+' instead of '||' in SQL. Also the join should read 'from sys.tables tjoin sys.columns c on (t.object_id = c.object_id)'
edosoft
If you need to be SQL-92 compliant, use INFORMATION_SCHEMA instead of the systable or sys.tables catalog views (those are SQL Server specific)
marc_s