SQL to XML with SSIS

Have you ever had a vendor ask for XML instead of a quick CSV file? Well, if you’re using MSSQL 2005 or 2008, I’m sure you realized just how awesome they are when this request came through. I’m going to walk through exporting an XML file from SQL Server 2005 using SSIS ans SQL.

First we’ll need to write the SQL script to generate the XML. I’m going to write an XML file that exports a list of users to a vendor so we’ll have root » customer » action » user info

Let’s start basic and just get something like this:

<root>
  <customer>demousername</customer>
</root>

To get this with SQL, it looks like this:

select 'demousername'
for xml path('customer'), root('root')

Really simple so far right? Okay, so I need to add two attributes to my customer element called username and password, which are provided by my vendor. So I’ll want my end XML to look like this:

<root>
  <customer id="demouserid" password="demopassword" />
</root>

To get this result, I simply write this SQL:

select 'demouserid' as '@id', 'demopassword' as '@password'
for xml path('customer'), root('root')

Okay, so now I need to add my action element like this:

<root>
  <customer id="demouserid" password="demopassword">
    <action ActionType="User_Add"/>
  </customer>
</root>

The SQL looks like this:

select 'demouserid' as '@id', 'demopassword' as '@password',
(select 'User_Add' as "@ActionType"
for xml path('action'), type) 
for xml path('customer'), root('root')

The only weird thing about this SQL is the “@ActionType”, which represents the attribute name on the action element. The @ sign basically says hey I’m an attribute and not an element. Also, the “type” after (‘action’), says that I’m an element. Without the “type”, you’ll get this:

<root>
  <customer id="demouserid" password="demopassword">&lt;action ActionType="User_Add"/&gt;</customer>
</root>

Okay, so now I need to add my user info and I’ll end up with this:

<root>
  <customer id="demouserid" password="demopassword">
    <action ActionType="User_Add">
      <FirstName>Deran</FirstName>
      <LastName>Schilling</LastName>
      <UserName>test@demo.com</UserName>
      <Password>demopassword</Password>
      <Email>test@demo.com</Email>
      <JobTitle>Something Awesome</JobTitle>
    </action>
  </customer>
</root>

So to get this XML, I just add in the columns and names I want like this:

select 'demouserid' as '@id', 'demopassword' as '@password',
(select 'User_Add' as "@ActionType"
, firstname as FirstName, lastname as LastName
, emailaddress as UserName, 'demopassword' as [Password]
, emailaddress as Email, jobtitle as JobTitle
from demousertable
for xml path('action'), type)
for xml path('customer'), root('root')

One more thing I want to show you is how to add a reference number to the action element. So I’ll end up with something like this:

<root>
  <customer id="demouserid" password="demopassword">
    <action ActionType="User_Add" ref="1">
      <FirstName>Deran</FirstName>
      <LastName>Schilling</LastName>
      <UserName>test@demo.com</UserName>
      <Password>demopassword</Password>
      <Email>test@demo.com</Email>
      <JobTitle>Something Awesome</JobTitle>
    </action>
    <action ActionType="User_Add" ref="2">
      <FirstName>John</FirstName>
      <LastName>Demoed</LastName>
      <UserName>john@demo.com</UserName>
      <Password>demopassword</Password>
      <Email>john@demo.com</Email>
      <JobTitle>Something Okay</JobTitle>
    </action>
  </customer>
</root>

Notice the ref=”1” and ref=”2” on the action element? The way you get that result is with this SQL:

select 'demouserid' as '@id', 'demopassword' as '@password',
(select 'User_Add' as "@ActionType", ROW_NUMBER() OVER (ORDER BY username) as '@ref'
, firstname as FirstName, lastname as LastName
, emailaddress as UserName, 'demopassword' as [Password]
, emailaddress as Email, jobtitle as JobTitle
from demousertable
for xml path('action'), type) 
for xml path('customer'), root('root')

So the ROW_NUMBER() OVER (ORDER BY uniqueidentifierofsomesort) gets the row number for each returned record. I love that feature.

Alrighty! So now let’s get to the SSIS, which is much simpler!

Step 1:
Create new SSIS package in Visual Studio 2005.

Step 2:
Drag an “Execute SQL Task” over to the “Control Flow” and name it something like “Generate XML” or whatever you prefer.

Step 3:
Double-click it.

• General Section
• Change ResultSet to XML
• Select/Create your Connection
• Enter the SQL statement, like the one we created above

• Result Set Section
• Add a new string variable called something like XmlContent

Step 4:
Drag a “Script Task” over to the “Control Flow” and name it something like “Save Generated XML” or whatever you prefer.

Step 5:
Double-click it.

• Script Section
• Add XmlContent (or whatever you named your variable) in the ReadOnlyVariables
• Click “Design Script…”

Step 6:
Once in the ScriptMain for VB, which I’m not a fan, but appears to be the only option. So you’ll just add this snippet in the Public Sub Main():

Dim sw As New IO.StreamWriter("C:\demo\AddUsers.xml")
sw.Write(Dts.Variables("User::XmlContent").Value.ToString())
sw.Dispose()
Dts.TaskResult = Dts.Results.Success

The red text above represents what you’ll need to change to match what you desire. First item is the file path to the location of your xml file and the second item is the name of your variable with the namespace.

That’s it! Now you have a generated XML file from a SQL DB. I thought it was pretty cool and a LOT easier than I thought it would be when I first started.

Thanks for reading!