SQLCMD with XML ON to output xml type column to file

/*

Suppose we need to export the data of an xml column from a table by TSQL, e.g. a batch or a store procedure. The xml length may exceed 8KB.

We can use sqlcmd together with :XML ON command.(See this entry)

*/

— TSQL batch:

DECLARE @sql nvarchar(1000)

SET

@SQL=‘sqlcmd -S(local) -E -dAdventureWorks -iC:\TestSQL.sql -oC:\testxml.txt -h-1 -y0’

PRINT

@SQL

EXEC

Master..xp_CmdShell @SQL

 

–C:\TestSQL.sql

:

XML ON

SELECT

Instructions FROM Production.ProductModel WHERE Instructions IS NOT NULL

/*

Hwoever, if you run the script, you will get the following message in the output file (textxml.txt):

<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>

HResult 0x80004005, Level 16, State 1

No description provided

*/

/*

This is because :XML ON can only stream one xml at a time. There are two solutions, one is loop through each qualified record in the table and stream the xml

*/

SET

NOCOUNT ON

SET

QUOTED_IDENTIFIER ON

SET

ANSI_NULLS ON

DECLARE

@ProductModelID int

SELECT

@ProductModelID=MIN(ProductModelID) FROM Production.ProductModel WHERE Instructions IS NOT NULL

WHILE

@ProductModelID IS NOT NULL

BEGIN

:

XML ON

SELECT Instructions FROM Production.ProductModel WHERE ProductModelID=@ProductModelID

:

XML OFF

SELECT @ProductModelID=MIN(ProductModelID) FROM Production.ProductModel WHERE ProductModelID>@ProductModelID AND Instructions IS NOT NULL

END

/*

Another solution is to build all the xml documents in all the qualified records into one big xml document without a root (xml freqments) and sqlcmd can stream it out by :XML ON

*/

–C:\TestSQL.sql

SET

NOCOUNT ON

SET

QUOTED_IDENTIFIER ON

SET

ANSI_NULLS ON

DECLARE

@out xml

SELECT

@out=(

SELECT

(

SELECT Instructions FROM Production.ProductModel

WHERE

Instructions IS NOT NULL

FOR XML PATH(),TYPE).query(‘Instructions/*’))

— Do not put :XML ON other wise getting the same error message

— :XML ON

SELECT

@out

/*

The FOR XML PATH builds the xml, but attached Instructions as the element name. The .query method removes the <Instructions> elements from the xml.

Remember we can use the .query directly after the FOR XML cluase. The .query method applies to the xml the FOR XML created.

*/

Advertisements
This entry was posted in XML, XQuery and XPath. Bookmark the permalink.

One Response to SQLCMD with XML ON to output xml type column to file

  1. Youjin says:

    ohhhh I many things to read!!  I will try to keep up!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s