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.

clip_image002

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.

image

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 (22)

  1. Mohammad says:

    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.

  2. JensS says:

    Hi, same syntax here, try:

    Select * from

    (

    select * from test

    for xml path(‘Customer’),root(‘names’)

    ) as yourname(ColumnNameHere)

    -Jens

  3. Mohammad Shahnawaz says:

    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.

  4. JensS says:

    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

    -Jens

  5. Basa Jozsef says:

    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.

  6. Antek S. Baranski says:

    Not sure how this is supposed to work on SQL2008 if I try this:

    SELECT [col_createDate] AS 'u/@t'

     ,[col_Id] AS u

     FROM [Users] AS FIFA

     WHERE [col_Id] IS NOT NULL

     FOR XML PATH(''), ROOT('root') AS Column

    Gives:

    Msg 156, Level 15, State 1, Line 7

    Incorrect syntax near the keyword 'AS'.

  7. volda says:

    THANKS!  With a few tweaks to fit my needs this gave me the XML output I needed.  Great post.

  8. Shaas says:

    cannot validate the xml file created using this method. An invalid character is being created probably a {CR}{LF}.

  9. Jens K. Süßmeyer says:

    Hi Shaas,

    can you send me your package to compare that with my template ?

    -Jens

  10. Arnaud says:

    Hi,

    I have an xml field in table, I send data on flat file with this method and the xml file is create.

    But it is not valid because the content is 1 big line.  

  11. Jens K. Süßmeyer says:

    Hi,

    I don´t think that the data is not valid due to one line, if there is a syntactical error, the problem is most likely due to the XML, maybe you are using a data type which chops of the content of the XML, therefore giving you an error.

    -Jens

  12. Carl Axberg says:

    The error with the file not vaildating seems to be an SSIS one. There is an extra whitespace after 4k of data.

    My guess is that the unicode text stream [DT_NTEXT] only holds 4k and when it's full it cuts the data and starts writing the next 4k on a new line…

  13. Rob says:

    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.

  14. Jens Süßmeyer says:

    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.

  15. Rob says:

    You put me on the right track. I've got it to work. Thank you very very much. Rob

  16. rodolfoop says:

    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 AnyColumnName

  17. Rajeesh says:

    Goto here you have a fully functional free XML Destination compatible with InfoPath form generation

    sites.google.com/…/XmlDestination.zip

  18. MRAFFERTY says:

    Brilliant! be lost without tutorials like this :)

  19. Susan says:

    Hi Jes, this solution works like really well, however, XML files tend to be really large how can you work around that? Thanks

  20. Ric says:

    Thank you! Been trying to figure this out for days. Exactly what I needed!

  21. Ryan says:

    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')