This week I had an interesting question: how to retrieve the structure of a table in XML format.
SQL Server, or to be more exact T-SQL, has provided support for XML since the 2000 version. The syntax is very simple, just add FOR XML to any SELECT query and you’re done. Actually you will probably have to add other parts to the clause in order to have the output meet your requirements.
The question was how to retrieve the schema. This can be achieved by adding “FOR XML AUTO, XMLDATA” when querying SQL Server 2000 databases or “FOR XML AUTO, XMLSCHEMA” when querying SQL Server 2005 and later versions – the XMLDATA clause has been deprecated. So our query will be similar to:
SELECT ... FROM ... FOR XML AUTO, XMLSCHEMA
The next step is how remove the data from the resultsand return only the schema. This can be achieved by adding the TOP clause to the SELECT statement as shown below:
-- SQL Server 2000 SELECT TOP 0 ... FROM ... FOR XML AUTO, XMLSCHEMA
And for the more recent versions:
-- SQL Server 2005 and later SELECT TOP (0) ... FROM ... FOR XML AUTO, XMLSCHEMA
Limiting the results to zero rows returns only the data types. An interesting trick!