Export XML Columns to Individual Files

,

I was reading SqlServerCentral.com Articles Requested forum and saw this request.

Looking for an article that takes data from an XML column in a table and exports each field as a separate document.

While the request did indicate a PoSH or SSIS method, I wanted to give it a try using T-SQL. Lets look at how I managed it.

 

The Setup

To get started, I do some requisite cleanup, declare a few variables and create a simple table to hold our XML column that we will be exporting.

USE DBA;
SET NOCOUNT ON;
declare @x xml
		, @cmd nvarchar(255)
		, @name varchar(100)
		, @OutputPath varchar(100) = 'C:\temp\'
/*** Basic CleanUp ***/
IF OBJECT_ID('DBA.dbo.MetaData') IS NOT NULL
	DROP TABLE dbo.MetaData
IF OBJECT_ID('tempdb.dbo.#Export') IS NOT NULL
	DROP TABLE #Export
/***  Create Table ***/
create table MetaData (
	id int IDENTITY(1,1) PRIMARY KEY,
	documentName varchar(100) NOT NULL,
	document XML not NULL
)

For data, lets query some of the system tables. In this case I’m going to create one XML document for Databases, Users, and Jobs.

/*** Fill our table ***/
select @x = (
	select 
		name, 
		create_date, 
		compatibility_level, 
		collation_name  
	from sys.databases [database]
	where 
		name in ('master','model','tempdb','msdb')
	for  xml auto, root('databases'), elements
)
insert into MetaData (documentName, document) values ('Databases', @x  )
select @x = (
	select 
		name, 
		type_desc, 
		create_date 
	from sys.server_principals 
	where principal_id < 258
	for xml auto, root('users'), elements
)
insert into MetaData (documentName, document) values ('Users', @x  )
select @x = (
	select 
		name, 
		description, 
		date_created 
	from msdb.dbo.sysjobs [job]
	where enabled=1 
	for xml auto, root('jobs'), elements
)
insert into MetaData (documentName, document) values ('Jobs', @x  )

 

And finally, we can use BCP to get our columns to disk:

 

CREATE PROCEDURE sp_DocumentToDisk 
	@documentName varchar(20),
	@Location varchar(200)
AS
BEGIN
	declare @cmd varchar(2000)
	set @cmd =  'bcp "SELECT document FROM DBA.dbo.MetaData where documentName=''' + @documentName + '''" queryout ' + @Location + '\' + @documentName +'.xml -c -T'
	EXEC xp_cmdshell @cmd
END

Using this procedure we can pass in a document name, and the location we wish to save the XML file and get our results by calling the procedure like this:

exec sp_DocumentToDisk 'Databases', 'c:\temp'
exec sp_DocumentToDisk 'Users', 'c:\temp'
exec sp_DocumentToDisk 'Jobs', 'c:\temp'

Which results in three new files sitting in our C:\Temp directory:

Xml_output

The contents of those files are simply the XML we have stored in the XML typed document column.

XML_JobOutput

Things to Think About

This approach is demonstrating the basic mechanics of how it would work, but there are a few things to keep in mind. Many DBAs will restrict xp_cmdshell, as they rightly should, as it can be very dangerous in the wrong hands, but with proper precautions and monitoring, it is a valid tool in your tool belt. One final thing to think about is permissions. When xp_cmdshell is run, it runs as the SQL Server Service Account, unless configured more precisely with sp_xp_cmdshell_proxy for instance. Which ever account that is being used will need permissions to write to the @Location you provide.

Comments, Suggestions

This is my approach using T-SQL, I’d love to hear feedback on my approach or holes in where my approach might be wrong. Thanks for stopping by.

Rate

Share

Share

Rate