Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« SQL Friday, The Best SQL Server Links Of The Past Week Episode 14

SQL Friday, The Best SQL Server Links Of The Past Week Episode 13 »

comments

Create XML Files Out Of SQL Server With SSIS And FOR XML Syntax

by SQLDenis on Mar 05, 2009 in categories Database Programming, Microsoft SQL Server

Instapaper

So you want to spit out some XML from SQL Server into a file, how can you do that? There are a couple of ways, I will show you how you can do it with SSIS. In the SSIS package you need an Execute SQL Task and a Script Task.

Let's get started

First create and populate these two tables in your database

tsql

Line number Off | Hide | Select all

create table Artist (ArtistID int primary key not null,
ArtistName varchar(38))
go
create table Album(AlbumID int primary key not null,
ArtistID int not null,
AlbumName varchar(100) not null,
YearReleased smallint not null)
go
insert into Artist values(1,'Pink Floyd')
insert into Artist values(2,'Incubus')
insert into Artist values(3,'Prince')
insert into Album values(1,1,'Wish You Were Here',1975)
insert into Album values(2,1,'The Wall',1979)
insert into Album values(3,3,'Purple Rain',1984)
insert into Album values(4,3,'Lotusflow3r',2009)
insert into Album values(5,3,'1999',1982)
insert into Album values(6,2,'Morning View',2001)
insert into Album values(7,2,'Light Grenades',2006)

Now create this proc

tsql

Line number Off | Hide | Select all

create proc prMusicCollectionXML
as
declare @XmlOutput xml
set @XmlOutput = (select ArtistName,AlbumName,YearReleased from Album
join Artist on Album.ArtistID = Artist.ArtistID
FOR XML AUTO, ROOT('MusicCollection'), ELEMENTS)
select @XmlOutput
go

After executing the proc

tsql

Line number Off | Hide | Select all

exec prMusicCollectionXML

you will see the following output

xml

Line number Off | Hide | Select all

<MusicCollection>
<Artist>
<ArtistName>Pink Floyd</ArtistName>
<Album>
<AlbumName>Wish You Were Here</AlbumName>
<YearReleased>1975</YearReleased>
</Album>
<Album>
<AlbumName>The Wall</AlbumName>
<YearReleased>1979</YearReleased>
</Album>
</Artist>
<Artist>
<ArtistName>Prince</ArtistName>
<Album>
<AlbumName>Purple Rain</AlbumName>
<YearReleased>1984</YearReleased>
</Album>
<Album>
<AlbumName>Lotusflow3r</AlbumName>
<YearReleased>2009</YearReleased>
</Album>
<Album>
<AlbumName>1999</AlbumName>
<YearReleased>1982</YearReleased>
</Album>
</Artist>
<Artist>
<ArtistName>Incubus</ArtistName>
<Album>
<AlbumName>Morning View</AlbumName>
<YearReleased>2001</YearReleased>
</Album>
<Album>
<AlbumName>Light Grenades</AlbumName>
<YearReleased>2006</YearReleased>
</Album>
</Artist>
</MusicCollection>

So far so good, so how do we dump that data into a file?
Create a new SSIS package add an ADO.NET Connection, name it AdventureWorksConnection
Drop an Execute SQL Task onto your control flow and modify the properties so it looks like this

Execute SQL Task

On the add a result set by clicking on the add button, change the variable name to User::XMLOutput if it is not already like that

Note!!! In SSIS 2008 this variable should be already created otherwise it will fail

Execute SQL Task Adding A Resultset

Now execute the package.
You will be greeted with the following message:
Error: 0xC00291E3 at Execute SQL Task, Execute SQL Task: The result binding name must be set to zero for full result set and XML results.
Task failed: Execute SQL Task
In order to fix that, change the Result Name property from NewresultName to 0, now run it again and it should execute successfully.

Our next step will be to write this XML to a file.
Add a Script Task to the package,double click the Script Task,click on script and type XMLOutput into the property of ReadWriteVariables. It should look like the image below

SSIS Script Task

Click the Design Script button, this will open up a code window, replace all the code you see with this

Line number Off | Hide | Select all

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
'
' Add your code here
'
Dim XMLString As String = " "
XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "")
XMLString = "<?xml version=""1.0"" ?>" + XMLString
GenerateXmlFile("C:\\MusicCollection.xml", XMLString)
End Sub
Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String)
Dim objStreamWriter As IO.StreamWriter
Try
objStreamWriter = New IO.StreamWriter(filePath)
objStreamWriter.Write(fileContents)
objStreamWriter.Close()
Catch Excep As Exception
MsgBox(Excep.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

SSIS 2008 requires a code change
Here is what the code should look like if you are running SSIS 2008

Line number Off | Hide | Select all

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
'
' Add your code here
'
Dim XMLString As String = " "
XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "")
XMLString = "<?xml version=""1.0"" ?>" + XMLString
GenerateXmlFile("C:\\MusicCollection.xml", XMLString)
End Sub
Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String)
Dim objStreamWriter As IO.StreamWriter
Try
objStreamWriter = New IO.StreamWriter(filePath)
objStreamWriter.Write(fileContents)
objStreamWriter.Close()
Catch Excep As Exception
MsgBox(Excep.Message)
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
End Class

There are a couple of things you need to know, the XML will be generated inside a <ROOT> tag, I am stripping that out on line 23 of the code, on line 24 I am adding <?xml version="1.0" ?> to the file. Line 26 has the location where the file will be written, right now it is C:\MusicCollection.xml but you can modify that.

So now we are all done with this. It is time to run this package. Run the package and you should see that file has been created.

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

Social Sitings

Article Views: 33979 views

52 comments

Comment from: rpf [Visitor]

Works like a wonder! Thank you sir!

04/22/09 @ 01:02

Comment from: Reuben [Visitor]

Nice Job! Great Post!

06/26/09 @ 09:41

Comment from: NetGolf [Visitor]

Great post! However, I got "out of memory" error when I tried to create an XML from a table with 3 million rows. Due to security concern, I was not able to use "bcp". What else can I do? Please help! Thanks!!

10/12/09 @ 15:12

Comment from: Charles Marche [Visitor]

· http://www.acmecontracts.com

I managed to get this to work on SQL 2005. But the script has many problems when running on SQL 2008. Any chance of posting a SQL 2008 version please???

Thanks.

11/27/09 @ 08:32

Comment from: SQLDenis [Member]

Charles,

what errors are you getting? I will run this on sql 2008 later today and will let you know if it works

11/27/09 @ 08:59

Comment from: SQLDenis [Member]

Charles I added the 2008 code changes

11/29/09 @ 18:32

Comment from: Charles [Visitor]

Hi SQLDenis,

Many thanks for the new code! It seems to execute as long as you keep the entrypoint to Main.

The output file has problems, it only contains 1 line which says xml version="1.0". Then the rest of the file is blank.

A couple of questions please,
1) In 2008 it doesn't create the Result Set entry for User:XMLOutput automatically. Is it meant to auto-detect this? from an output from the Stored Procedure?

2) As a solution it seems complicated for just outputing an XML file. I'd heard great things about SSIS, but it seems extremely complex - probably rightly so.

11/30/09 @ 19:02

Comment from: StanleyG [Visitor]

Thanks for sharing. This was helpful. What determines the encoding (e.g. UTF-8, UTF-16) of the final output file using this process? Can one specify what they want the encoding to be?

12/13/09 @ 20:26

Comment from: Ted Krueger (onpnt) [Member]

You'll need to set it in the script task using Encoding
http://msdn.microsoft.com/en-us/library/ms143456.aspx

12/14/09 @ 06:20

Comment from: StanleyG [Visitor]

Thanks for the response, onpnt. I will review.

12/15/09 @ 00:43

Comment from: Tony [Visitor]

Found this article after much searching on Google. It is sad that there isn't a native component for writing out a file within SSIS, but at least the solution is a very simple script. Thanks for this!

01/13/10 @ 11:19

Comment from: Greca [Visitor]

This article was very useful for me, especially the trick on line 23 …. Thank you very much

Greca

01/18/10 @ 01:23

Comment from: Slick [Visitor]

How can I date stamp the file name?

musiccollection_04212010.xml or similar?

thanks,
s

04/28/10 @ 12:38

Comment from: SQLDenis [Member]

Slick,

Like this

Dim FileName As String
FileName = "musiccollection_" & Today.ToString("MMddyyyy") & ".xml"

and then instead of this

GenerateXmlFile("C:\\MusicCollection.xml", XMLString)

do this

GenerateXmlFile(FileName , XMLString)

04/28/10 @ 12:58

Comment from: Slick [Visitor]

Thanks for your reply.

Sorry I'm weak with programming languages.

Thank you so much again.

S

04/28/10 @ 13:35

Comment from: Gerry [Visitor]

Wow. thanks this is great.

How do I now call these packages I have created to run on a schedule?
So that the XML populates in a folder everyday or every hour?

05/21/10 @ 14:55

Comment from: SQLDenis [Member]

Gerry, you can create a SQL Agent job and schedule that or start the job from a procedure/SQL with sp_start_job

05/22/10 @ 04:29

Comment from: Sainis [Visitor]

Hey,Can anyone please help me on how can I do the same task using OLEDB Connection instead of ADO.Net.

06/09/10 @ 21:14

Comment from: CVN [Visitor]

Hey,

This works great. Thanks a lot.

Can somebody tell me how to add a namespace?

Example:
ns0:MusicCollection xmlns:ns0="http://ABC.Data.AX.v01"

Thanks in advance,
CVN

07/08/10 @ 09:04

Comment from: BravehearT1326 [Visitor]

Hi - seems to be just what I'm looing for but cant get it to work!!!

In your posting you say "Drop an Execute SQL Task onto your control flow and modify the properties so it looks like this" but there is no picture or instructions on the values to add etc.

and when I create a script task for SQL 2005 and paste in your code all I get is the ?XML Version row written to the file not the results from the stored proc.

Can you advise what I'm mising?
Thanks in advance

09/17/10 @ 02:14

Comment from: BravehearT1326 [Visitor]

Got it working after spending some time fiddling around with the results sets options etc.

Thanks this has saved a whole lot of hassle.

09/17/10 @ 03:16

Comment from: John [Visitor]

Very useful article, thanks!

04/11/11 @ 02:55

Comment from: Jason [Visitor]

Great article!

I'm so close, but I'm having an issue where the file created only shows this:
'xml version="1.0" ?>System.Xml.XmlDocument'

I verified the query in my procedure and when run from SQL 2008, it produces results.

How can I show the data in the destination .xml file?

Thanks!

05/04/11 @ 09:49

Comment from: Slick [Visitor]

Hi, I had a question.

Where does the "Artist" and "Album" element come from? In your SP select there is only ArtistName and AlbumName which are children.

I'm sure it doesnt just randomly/magically appear under those parents. So I was wondering if you could please tell me how that shows up in the XML file.

Thank you,
S

05/25/11 @ 14:25

Comment from: Slick [Visitor]

Oh!! The parent elements are the table names! DOH!!!

05/25/11 @ 14:27

Comment from: SQLDenis [Member]

Artist and Album come from the table names itself

05/25/11 @ 14:28

Comment from: anki [Visitor]

i was able to create xml file using your method.it works great.but in my xml file i have the expandable button being display like if i click on '+' sign it displays all the nodes under it,if i again click on it it hides the nodes.I dont want this functinality in the result.How can i avoid this?

07/05/11 @ 14:09

Comment from: SQLDenis [Member]

Open the XML file in a parser or notepad/editplus/notepad++ instead of Internet Explorer

07/05/11 @ 14:48

Comment from: anki [Visitor]

thanks for your reply Dennis.
I am trying to add timestamp to the xmlfile name ,i am using the below syntax in vb.net
C:\users\result_" & Today.ToString("ccyymmdd") & ".xml"
but the resultant filename is result_cc110007 which is not the right one.Can you please tell me if i am going in the right way?

07/07/11 @ 15:09

Comment from: SQLDenis [Member]

mm has to be upper case MM
try yyyyMMdd

07/07/11 @ 17:23

Comment from: sqldeveloper [Visitor]

Dennis,
i am trying to add namespace to the header of the xml file.I want to add this as the default namespace in the header but with no prefixes for the child nodes.I want this namespace to be a part of the header file it should be seen only there but not anywhere in the xmlfile.Below is the example:
root xmlns="http://www.w3.org/"
tr
tdApples/td
td>Bananas/td
/tr
/root
\i edited the example because it was not allowing be to post using the symbols.
I tried doing this using sql server but it didnot work for me ,is there any code in vb.net so that i can add it in the script task before the xml file is generated.I am using same vb.net code that is given in your bolg to generate the xml file.

07/12/11 @ 11:02

Comment from: Porter [Visitor]

I am running SQL 2k8 R2, and having the same problem as others with only the header appearing in the output file

Running the code in the SQL task does return rows from a table column of type xml

07/14/11 @ 12:53

Comment from: MMeyer [Visitor]

For adding Namespaces you can use an RegEx Replace before you Output the File:

XMLString = System.Text.RegularExpressions.Regex.Replace
with 3 Arguments (String1, String2, String3)
String1 = XMLString (the same String-Variable that stands before =)
String2 = SearchString = your tag to Add Namespace into
String3 = the ReplaceString must consist of Tag(Space)xmlns="Namespace"

Remember to use The greater and lesser symbols around the Tag (could'nt post an Exact Example here, because it wouldn't let me use the symbols).
The Regex.Replace Method Replaces ALL Matches for String2 with String3 so be carefull, but if you only add a NameSpace xmlns to your root Tag then there shouldn't be a Problem.

Example for the 3 Arguments with # instead of greater or lesser symbols
(XMLString, "#ROOT#", "#ROOT xmlns=""Namespace""#")
Remember to Escape your " with another " or three if you add a Variable between:
(XMLString, "#ROOT#", "#ROOT xmlns=""" + Variable + """#")

08/17/11 @ 01:32

Comment from: Vasu [Visitor]

Gr8 post. But when i try to design the script, i get the error like "namespace does not directly contain members such as fields or methods". I tried every possible way but failed. I am using SSIS 2008. please help me.

09/09/11 @ 00:32

Comment from: Vasu [Visitor]

Hi Again. After Running the package, only header is appearing in the output xml. And I think most of us are getting the same output.. Please respond.

09/09/11 @ 05:12

Comment from: SQLDenis [Member]

If your file only contains the header it means that the XML variable is empty

after this line

XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<root>", "").Replace("</root>", "")

can you do a messagebox.show

MessageBox.Show(Dts.Variables("XMLOutput").Value.ToString)

now if the variable is blank you know what the problem is and you have to figure out why the variable wasn't populated

09/09/11 @ 07:59

Comment from: gary [Visitor]

Your post has greatly saved me time. I am very grateful. On implementing this, I realized that somehow, my local machine is not configured the same as yours because I needed to change the SQLSourceType to Variable and set the IsQueryStoredProcedure to False. Otherwise, I get the error "Object reference not set to an instance of an object". Aside from this issue, I got it working spitting out XML files when combined with For Each Loop Container. May your tribe increase!!!

09/09/11 @ 08:53

Comment from: Andrea [Visitor]

Thank you! It works. I developed the package with VS2008 today.

09/14/11 @ 04:25

Comment from: CY [Visitor]

Hi , I have this error below :

[Execute SQL Task] Error: Executing the query "prMusicCollectionXML" failed with the following error: "Exception of type 'System.Exception' was thrown.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

10/12/11 @ 05:41

Comment from: Amal [Visitor]

· http://amal.net

I've been tinkering with XML on SQL 2008 R2 for a few hours now and I'm still not quite sure what I'm doing. I have a value storage table with 3 fields;

GUID, Name, Value

The GUID field is a uniqueidentifier data type, and refers to an event record in another table, so there could be multiple records in the value storage table for each GUID that each specify a name/value pair. For example;

B8B6487C-66E0-42B6-B667-92F79A45E544 Pressure 3812.3
B8B6487C-66E0-42B6-B667-92F79A45E544 Temperature 24.2

What I'm trying to do is create a simple query that will output formatted XML in the following format;

<output%gt;
<GUID%gt;B8B6487C-66E0-42B6-B667-92F79A45E544</GUID%gt;
<Pressure%gt;3812.3</Pressure%gt;
<Temperature%gt;24.2</Temperature%gt;
</output%gt;

But all I seem to be able to get is something like;

<output%gt;
<GUID%gt;B8B6487C-66E0-42B6-B667-92F79A45E544</GUID%gt;
<Name%gt;Pressure</Name%gt;
<Value%gt;3812.3</Value%gt;
</output%gt;
<output%gt;
<GUID%gt;B8B6487C-66E0-42B6-B667-92F79A45E544</GUID%gt;
<Name%gt;Temperature</Name%gt;
<Value%gt;24.2</Value%gt;
</output%gt;

... which is not what I want. Have any ideas? Thanks in advance!

10/18/11 @ 13:06

Comment from: Dustin [Visitor]

Thank you so much for this code you have saved me hours of development time.

03/30/12 @ 14:25

Comment from: Shane [Visitor]

Thanks alot with a few modifications it worked.
Modifications:

Note!!! In SSIS 2008 this variable should be already created otherwise it will fail
- I changed it USER::Variable (String & placed XMLOutput in value)
- then with Script Task (selected USER::Variable chkbox & changed 'XMLString = Dts.Variables("XMLOutput")' to ("Variable")
Thanks

04/05/12 @ 12:06

Comment from: Joe [Visitor]

After many hours of fighting this, I finally got it to create a file!!!!!!!!!!

Thanks for the post!!!

Joe

06/20/12 @ 09:23

Comment from: jayanthcavin@yahoo.com [Visitor]

After a lot of internet browsing and learning I found your explanation to be the beast in accomplishing the task. Appreciated. Last one question when I open the stored XML file my elements are not formatted but when I open the same file using IE everything looks formatted. How can be able to see the same formatted output when I viewing through my XML editor as well. Thanks in advance.

06/27/12 @ 09:10

Comment from: Kishor Kumar [Visitor]

Thanks Sir gr8 job

08/01/12 @ 07:42

Comment from: Jayanth [Visitor]

If you want to add NameSpaces to the RootNode then you can do it in the following way.Just replace # with Greaterthan and Lesserthan symbols.

Dim XMLString1 As String
Dim NameSpaces1 As String
Dim NameSpaces2 As String
Dim NameSpaces3 As String
NameSpaces1 = "https://uidataexchange.org/schemas"
NameSpaces2 = "http://www.w3.org/2001/XMLSchema-instance"
NameSpaces3 = "https://uidataexchange.org/schemas combined.xsd "

XMLString1 = System.Text.RegularExpressions.Regex.Replace(XMLString, "#StateSeperationRequestCollection#", "#StateSeperationRequestCollection xmlns=""" + NameSpaces1 + """ xmlns:xsi=""" + NameSpaces2 + """ xsi:schemaLocation=""" + NameSpaces3 + """ #")
GenerateXmlFile(FileName, XMLString1)

Just a working copy.

08/09/12 @ 09:50

Comment from: Steve [Visitor]

I also can't get the xml file to read anything but:xml version="1.0" ?>System.Object

Why would this be...so close...help!!! Thank you.

11/21/12 @ 11:16

Comment from: SQLDenis [Member]

Steve,

If your file only contains the header it means that the XML variable is empty

after this line

XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("", "").Replace("", "")

can you do a messagebox.show

MessageBox.Show(Dts.Variables("XMLOutput").Value.ToString)

now if the variable is blank you know what the problem is and you have to figure out why the variable wasn't populated

11/21/12 @ 11:19

Comment from: mim [Visitor]

how to write the syntax if i have few tables and i want
they all to be the same level, for example i have album table and also pictures table and i do not want pictures to be under album, any one has an idea?

12/03/12 @ 04:10

Comment from: SQLDenis [Member]

Min, take a look at Use FOR XML AUTO,TYPE, ELEMENTS to get XML in the format you really want with SQL Server FOR XML Syntax to see how you can do it

12/05/12 @ 08:07

Comment from: jampa [Visitor]

thanks Dennis, we are implementing XML targets now . you'll see me on your blog quite often.

Thank you !!

04/29/13 @ 11:28

Comment from: nadeem [Visitor]

Hi, thankyou for the wonderful post, however can you please provide me the C# code instead of VBCode. I appreciate your help.
Thanks

05/09/13 @ 11:58

Comment feed for this post

LessThanDot

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

Create XML Files Out Of SQL Server With SSIS And FOR XML Syntax

About the Author

52 comments

Leave a comment

LessThanDot

LTD Social Sitings

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

Create XML Files Out Of SQL Server With SSIS And FOR XML Syntax

About the Author

52 comments

Leave a comment