An “secret” SSIS XML Destination Provider you might not found yet
(Sample code included at the end of the post)
The initiator for this post was Dan Atkins who wanted to create a feed from relational data to consume it directly from a created gadget.
Where can I find that in the toolbox ?
First of all, you won’t a XML destination adapter as of the shipped components in SQL Server 2005 and 2008. There are for sure third party components which can directly convert data from the data pipeline to defined XML but sometimes it is much easier than that and you just want to create an XML file from any data source which is able to produce XML look-a-like data. What does that mean ? Let me show you in a quick sample.
Many people are not aware of the great XML handling relational databases like SQL Server are capable of. They can generate XML data from a relational set / query and give you the string representation or the binary data to work with.
The older brother of XML
So the destination should be a XML file, right ? How would you describe a XML file in comparison to any other file type like a Word-Document ? Well, compared to a Word document, XML can be opened and read in plain text with any reader like notepad. At the end it is simply a flat file with clear text data. The older brother of XML files is a CSV file which can be produced by SSIS using a Flat File destination. Not touching the logic of XML files, it can be compared to a CSV with one column of data (That is really a high-level view :-)
But that is the direction this sample will talk about. We want to get data from a source that can produce XML Data representations (which can also be script tasks) and create an XML file from that. (See my former blog post on that here)
Creating the sample SSIS package
The source
For that I create a SSIS package with a simple OLEDB source.
As I wanted to make it easily reproducible for you without having the need to create a Northwind or Adventureworks database on your machine, I used the new feature of row constructors in SQL Server 2008 which is able to create a table on the fly within a query (very handy if you don’t want to persist static data which is only used for one single purpose). Notice that I created a full XML set with a root and several nodes. If you execute this in the execution engine of your choice, you will already get a nice XML representation. Depending on your needs, you might want to put some data in attributes instead of nodes, but that is all described in he blog entry below.
Notice that I put a SELECT ( XMLQueryHere) AS YourColumn in the query, as this will directly bring back the text representation of the XML to the output. Without that you will get binary data (System.Byte[]) which might not be the right choice in that situation. I addition, the created column names will have GUIDs within if you do not use this notation making it hard to have a predictable column name for the mapping later on.
The target
The target is even simpler than the source. Map the output of the source to the flat file destination and open the editor of the flat file destination. It is a flat file destination (created as a UNICODE file) create manually a column of type DT_NTEXT (1). Deselect the Option “Column headers in the first row” to get the pure value of the XML. Navigate to the Flat file destination adapter and map it in the Mappings section the XML input column to the destination flat file column and you are already done.
The result
Running that will bring you the pure XML created by the relational engine (in t´hat case SQL Server). I a aware that this isn’t the 100% perfect pipeline version of the XML adapter, but sometimes this is already enough to make data interchangeable with other partners and prevent you from using bcp and dynamic SQL execution at all.
The sample SSIS package can be downloaded here.
Comments
Anonymous
November 10, 2009
Dear Sir, The above details is exactly what i am looking but i am using SQL 2005 version but not SQL 2008. Can you pelase tell me how can i write the same query in sql 2005.? below is an example.. select * from test for xml path('Customer'),root('names') as Yourname In above case as yourname alias name is not accepting and if i dont use alias i am getting xml in binary format which i have issues in transforming in SSIS. many thanks for your help.Anonymous
November 10, 2009
Hi, same syntax here, try: Select * from ( select * from test for xml path('Customer'),root('names') ) as yourname(ColumnNameHere) -JensAnonymous
November 10, 2009
I tried the above syntax but my data is getting truncated and i am unable to see all the rows from my Test table. i had the same problem when i used varchar(max) variable the below example. declare @ssql varchar(max) SET @ssql ='' set @ssql =@ssql +(select * from test for xml PATH ('Customer'),ROOT('names')) select @ssql as XMLData if i use xml data type instead of varchar(max) then i can see all the rows from my Test table. But xml data type will not work for me as it give data in binary format. Please advice. Thanks in advance.Anonymous
November 10, 2009
Use my query I sent previously and don´t be confused what you see in SSMS. The output will only be truncated there, not in the actual output to the client. SSMS has some restrictions which can be found under Tools > Options > Query Result > Results To Grid / Results To Text -JensAnonymous
January 31, 2010
Good, but what if I want to make any transformations on data after getting it from the source system, then bring the transformated data to XML format? After the transformation I must stage the data on sql server and extract it again to an XML format? Quite complicated. Microsoft should develop XML destination adapter.Anonymous
April 14, 2010
The comment has been removedAnonymous
April 30, 2010
THANKS! With a few tweaks to fit my needs this gave me the XML output I needed. Great post.Anonymous
October 14, 2010
The comment has been removedAnonymous
October 15, 2010
Hi Shaas, can you send me your package to compare that with my template ? -JensAnonymous
November 25, 2010
The comment has been removedAnonymous
December 05, 2010
The comment has been removedAnonymous
December 15, 2010
The comment has been removedAnonymous
December 20, 2010
My SQL command task start with ;WITH XMLNAMESPACES (default ) within the select statement Do you have a solution for that. The SSIS (data flow task) fails.Anonymous
December 21, 2010
I hope you are watching the thread. You will need to devide the inner and outer part: ;WITH XMLNAMESPACES ( 'http://YourNamespace' AS ns0 ) SELECT Data = ( SELECT T.n.value('(./NodeValue)[1]','SMALLINT') FROM @xml.nodes('//Path1/path2') AS T(n) FOR XML PATH(''), ROOT ('Data') ) Let me know, if you got it to work, Jens.Anonymous
December 21, 2010
You put me on the right track. I've got it to work. Thank you very very much. RobAnonymous
April 20, 2011
This is the easiest way to export to a xml file I found. Just use any query and sourround it by the following and effectively, you get text instead of binary data. Thank you very much, Jens. SELECT ( YourQuery ) AS AnyColumnNameAnonymous
August 01, 2011
Goto here you have a fully functional free XML Destination compatible with InfoPath form generation sites.google.com/.../XmlDestination.zipAnonymous
May 16, 2013
Brilliant! be lost without tutorials like this :)Anonymous
October 20, 2013
Hi Jes, this solution works like really well, however, XML files tend to be really large how can you work around that? ThanksAnonymous
February 25, 2014
Thank you! Been trying to figure this out for days. Exactly what I needed!Anonymous
January 15, 2015
I have this setup, but when I open my created XML file it is not formatted like XML...it is all jumbled! Here is my SQL Select in my Source... SELECT [StoreInventoryID] AS A ,[StoreID] ,[ProductID] AS B ,[QtyOnHand] ,[QtyDefective] ,[QtyTradeHold] ,[ModifiedDate] ,[CreatedDate] ,[QtyReservationHold] ,[QtyHopsHold] ,[QtyTradeHoldDefective] FROM [StoreInformation].[dbo].[StoreInventory] WHERE StoreID = 8127 FOR XML PATH('Item'),ROOT('ShipNode')