Home » Blog » Selecting the entire Database as XML String

Selecting the entire Database as XML String

2012-01-13 - General, XML

I just ran into this question on StackOverflow: Dump data into single XML file from MS SQL Server 2008 R2.
In there Daniel is looking for a solution to export his database as an XML file.

I thought that request was an excellent way to practice my XML skills so I spend a little time to come up with a solution.

For testing purposes I first created a few tables in an empty database:

[sql]

DECLARE @cmd NVARCHAR(MAX) ;
SET @cmd = ( SELECT 'CREATE TABLE T' + CAST(n AS NVARCHAR(MAX))
+ '(id INT IDENTITY(1,1)'
+ (SELECT ',c' + CAST(c.n AS NVARCHAR(MAX))
+ ' INT DEFAULT ' + CAST(c.n AS NVARCHAR(MAX))
+ '*' + CAST(t.n AS NVARCHAR(MAX))
FROM dbo.GetNums(( t.n - 1 ) % 10 + 1) c
FOR XML PATH('') ,
TYPE).value('.', 'NVARCHAR(MAX)') + ');INSERT INTO T'
+ CAST(n AS NVARCHAR(MAX)) + ' DEFAULT VALUES;'
FROM dbo.GetNums (3000) t
FOR XML PATH('') ,
TYPE).value('.', 'NVARCHAR(MAX)') ;

EXEC(@cmd) ;
[/sql]

This snippet uses Itzik's GetNums function to generate 3000 CREATE TABLE and INSERT statements.

With the table in place I was able to come up with this solution to the original question:

[sql] DECLARE @cmd NVARCHAR(MAX) ;
SET @cmd = 'SELECT * FROM('
+ STUFF(
(SELECT ' UNION ALL SELECT ''' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
+ '.' + QUOTENAME(name) + ''' [@name],' + '(SELECT * FROM '
+ QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(name)
+ ' FOR XML PATH(''row''),TYPE) data'
FROM sys.tables
FOR XML PATH('') ,
TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')
+ ')X FOR XML PATH(''table''),TYPE;' ;

EXEC(@cmd) ;
[/sql]

The script builds a single statement. For each table it selects the table name in a column with the name [@name] and the content of that table as XML (using a sub-select) in a column with the name data. All these SELECTs are "UNION ALL"ed together and formated into the final XML document with an additional outer SELECT FOR XML.

If you are only after some of the tables, you can add a where clause in row 8 of the above snippet.

The resulting XML has this format:

[xml] <table name="[dbo].[T1628]">
<data>
<row>
<id>1</id>
<c1>1628</c1>
<c2>3256</c2>
<c3>4884</c3>
<c4>6512</c4>
<c5>8140</c5>
<c6>9768</c6>
<c7>11396</c7>
<c8>13024</c8>
</row>
</data>
</table>
<table name="[dbo].[T2102]">
<data>
<row>
<id>1</id>
<c1>2102</c1>
<c2>4204</c2>
</row>
</data>
</table>
[/xml]

For each table there is a <table> tag that has a name attribut with the table name. It contains a <data> tag that in turn contains one <row> tag for every row in the table.

The only thing missing to make this valid XML is a root tag, but that should be easy to add.