December 5, 2008 at 11:02 am
Download the 30 day trial and save yourself some grief and aggravation...
Beware of N_TEXT fields though, CAST them as CHAR if you need to convert one.
Trust me...I just did this. Saved me a week.
April 20, 2009 at 5:56 pm
Another not so pretty option is to use Control flow (not Data flow), create SQL script task(s) to create your Dataset (if you want multiple tables to form a nested XML file, use 2+ SQL scripts each creating its own Dataset (which can only contain 1 table each, limitation of SSIS), then have them all flow into another SQL Script task which merges the 2+ datasets into 1 dataset, creates the relations, then calls the Dataset's method to spit out the XML. Not pretty because you don't get the nice functionalities of the Data flow.
But still a better design than having SQL spit out the XML where you can't manipulate it after the fact.
If you want to do this just for one simple table (i.e. no XML nesting) then find the code on the web that simulates an XML Destination Adapter. I haven't found any code yet that can do this for XML nesting, but it is in theory doable as an XML Destination Adapter...
April 22, 2009 at 11:21 am
Regarding Tim's original question, I’m sharing this because it drove me crazy for a week. Hope it's useful.
I used a method similar to Dan’s, outputting to a flat file with a .xml extension.
(and thanks to Jamie's blog at http://blogs.conchango.com/jamiethomson/archive/2006/07/11/4209.aspx).
What I struggled with, however, was the "simple" task of creating the FOR XML SQL select statement.
I had a not-too-complicated form to produce; the ID numbers are the result set of the query:
Template
123
456
789
This ended up being in a SQL 2005 sproc, partially recreated here, that I call with an Execute SQL task.
-- One output param
@FullXML xml OUTPUT
--Within the create procedure statement is this select.
SET @FullXML =
(SELECT CAST(
(SELECT 'AppID' AS '@ApplicationID','ProdID' AS '@ProductID'
,(SELECT CAST(
(SELECT 'Template' AS 'TemplateID'
,(SELECT CAST(
(SELECT ID as 'User/ID'
FROM
IDTABLE GROUP BY ID
FOR XML PATH(''), ROOT('Users'))
AS XML))
FOR XML PATH('Notify'))
AS XML))
FOR XML PATH('List'), ROOT ('NotifyList'))
AS XML)
) --END SET @FullXML
Select Cast(@FullXML as varchar(max));
Varchar(max) seems to be the way to avoid truncation of the XML.
From there it's just streaming it to the output file.
Regards,
Matt
April 22, 2009 at 11:23 am
Sorry, my XML form got stripped. Not sure how to reproduce that in the forum. Probably forbiddent characters or format. The SQL statement tells the tale.
Matt
June 12, 2009 at 8:37 am
Sorry if i'm hi-jacking a post, but it seems close to what i'm looking for....
How can I stop SSIS from wrapping the xml in ROOT tags? I've looked int the XML Task and can't see anything blatently obvious. Otherwise, I shall have to write a script to remove the first and last 6 characters - messy I know!!
If this has been posted elsewhere, and for ruining the thread - please accept my appologies!!
Kindest Regards,
Jim
June 12, 2009 at 9:39 am
You can remove the root tags in the script. Be sure to include the brackets > < around ROOT and /ROOT inside the quotation marks, they won't print in this post if I include them in the example below.
'The result comes back with wrapped in ROOT so strip that off (we named a root already in the proc)
XMLString = Dts.Variables("XMLvar").Value.ToString.Replace("ROOT", "").Replace("/ROOT", "")
Regards,
Matt
June 12, 2009 at 10:52 am
Yeah...I said that.
Like...a year ago. Way to follow the thread.
As for an update:
Keelio gets the job done as an easy trick in SSIS but bear this in mind. The LICENSE has to be current. If it is not, all jobs USING Keelio will suddenly STOP. So don't try the old download the demo for a quick project and decide not to buy, crap. You will feel pain in 30 to sixty days when the license expires.
Consider this a heads up for those of us in a hurry who get the download, get the project done and pass the payment of license issues on to accounting in the hopes of eventual payment. If the paid license isn't up in time...there will be "downtime" associated with a perfectly running SSIS package, and this WILL be the cause of the issue.
July 8, 2010 at 1:48 am
Vic K (4/14/2007)
Is there a way to get SSIS to not include the <ROOT> element? I am already defining this in my Stored Proc returning the XML.
Hi, is anyone aware of a way to do this? I have the same problem
Ignore me, Spain/Germany hangover....
Shot
Mike
July 26, 2010 at 11:45 am
Here's a solution I found when writing xml files
Create a script task passing in your xml string as a variable (along with any file names etc). Add a reference to System.XML and add the declaration to the your script task. Then use the xml document interface for creating your xml documents. Note - Resultset is set to XML in the SQL Task Editor for this.
In the Script header
Imports System.Xml
Dim xmlData As String = Dts.Variables("XMLVariableName").Value.ToString()
DIm xmlFile As String = Dts.Variables("XmlFileVariableName").Value.ToString()
xmlData = xmlData.REPLACE("<ROOT>,"").REPLACE("</ROOT>","")
Dim doc as new XmlDocument()
doc.LoadXml(xmlData)
' If you want to add the xml declaration string to your file add this
Dim declaration As XmlDeclaration
declaration = doc.CreateXmlDeclaration("1.0", "UTF-8", "")
documentRoot = doc.DocumentElement
doc.InsertBefore(declaration, documentRoot)
'Save the Xml document
doc.Save(xmlFile )
The one advantage to using this scenario is the xml gets verified when you call LoadXml which throws an error if the xml is not well formed
NF
July 28, 2010 at 2:17 pm
Or you could just use the template task provided for in the XML SSIS Toolkit available at http://www.keelio.cominstead of writing lots of code, SQL, etc to get the same effect.
July 30, 2010 at 1:27 am
I've had the same problem - this was a really clean way to acheive what I needed:
http://sqlblogcasts.com/blogs/simons/archive/2009/06/23/Exporting-XML-data-from-SSIS---nugget.aspx
May 27, 2011 at 6:58 am
I followed the advice put out regarding Keelio to see if I could get an XML destination file created quickly and easily.
I can say that Keelio is of no use what so ever if you are not planning to buy a license. Downloading and installing the 30 demo to see if it will give you what you need is far more hassle than trying some of the other, free suggestions littered throughout this thread.
From googling, I can see that there are multiple boards asking the same question, with a number of posts advising to try Keelio. Looks like some sort of dumb marketing ploy. The post above mine worked for me, was free and quick.
May 27, 2011 at 7:57 am
r.saintclaire:
I promise you it is not some dumb marketing ploy. Licensed users swear by it. We use it here at our company. We discovered it because other users having our problem recommended it on the forums. As for downloading and installing the 30 day demo...I did it myself. I was done in fifteen minutes. It took less than two hours to get good at it, and our custom Google feeds, and internal search (we use SLI) feeds are running like a charm. But all pay-for-play software is of no use whatsoever if you are not planning to buy a license. That's not Keelio's fault. That's how it is supposed to work. If you're not going to pony up the dough, then you can't have the solution.
THAT BEING SAID:
You are right on about rhoskins solution. it is elegant, let me save our readers the trouble of linking:
Quoted from: From Simon's Sql Blog
James Rowland-Jones asked me this evening how to export XML data from SSIS. I've done this before but always used the Script component and I wondered if there was another way.
I tried to think of a component that saves data to a file. Oddly there isn't a control flow task that does that. The nearest I came to was the "export column" transform, this is a hardly known transform. It was a very specific function that most people don't need. For each row of data going through it, it exports the contents of a column to a file with a filename specified in another column.
Thinking of this I generated an example query that returns a rowset with a filename column and our XML column (data).
select 'c:\test.xml' filename
, (select name, (select name
from sys.columns c where c.object_id = o.object_id
for xml path('column'),type) columns
from sys.objects o
for xml path('object'), root('schema'), type) data
This can then be plugged into an OLEDB source, connected to an Export Column transform configure the data column, filename and whether to append or truncate and away you go.
On Simon's page, there was apparently a video, but the link seems to be broken.
Now, all of that is well and good. You can buy Keelio if you have a larger project, time is at a premium and you have the money, but be warned - IT IS ANNUAL. if you wake up one day and all of your XML jobs stop working, find out which email box got the 30 day reminder email to pay and ignored it. Then beat that person with a wet noodle. If you have a small thing to do, by all means, use the select rhoskins put out there for the "onesies" and "twosies". But to really hammer the nail in terms of getting the XML file you want on demand, like Keelio, and do it internally to SQL, you've gotta get cozy with XML EXPLICIT. You can read what MSDN says, but their take on it is informational in design. It is reference material for those alreadt familiar with the concept. Mostly usable for little else than for syntax reminders. If you to get familiar with the concept it in short order, Jacob's Blog has what I think is the best hands-on quickie tutorial on how to get the XML file you want.
http://beyondrelational.com/blogs/jacob/archive/2007/10/14/for-xml-explicit-tutorial-part-1.aspx
Sadly, he doesn't go into detail about WHY the use of the TAG element, but it becomes apparent later.
On the bottom of the page, there is the link which goes through the steps which includes "FOR XML TUTORIALS". You will find some great info there. One of the things I like about Jacob's stuff is that he starts from a blank slate, building the tiny database at the beginning, allowing the novice user to start with SQL concepts they are very familiar with, and correlating it well with the shift to XML. Pay particular attention because he begins with the end result you are looking for, and then jumps to building the database and the query, there is no "incremental" build of the XML.
Jacob's blog has great stuff, but it is a hot mess. Looking for specific answers is similar to trying to find a matching sock in my kid's room. You KNOW it is in there somewhere, but do you have the strength and patience to dig for it? Not usually...
June 1, 2011 at 8:33 pm
The software at Keelio.com is not annual subscription based. Once you purchase it you have it for good, however in order to get updates and support you need to maintain your maintenance. The product does NOT time out after your maintenance has lapsed.
I guess it mainly comes down to how fast you think you can get it done. Write lengthy SQL scripts or learning the Template transformation tool that comes with the XML SSIS toolkit at keelio.com.
June 2, 2011 at 7:31 am
Technically, you're right. They do not charge you to keep the license, the program did not stop working - as it turns out - because keelio turned off...we transferred the project to a new server, as we have done annually, due to our growth (went to check with the department about why we are paying a bill the MOMENT I read this...).
In the first two years, something similar to this has caused us to require support, due to a change in something, somewhere (in our case it was a migration from one server to another - twice). It is at this point that we were made aware of the "current" version, each time, which we apparently needed to be on, and how the support cost allowed that upgrade to be included.
In an IT department our size, you buy software that maintains your enterprise WITH the appropriate support agreements in place. It's just good business., and should be a budget line item. We get a bill every year...and we HAPPILY pay it.
But you're right, it was confirmed for me that Keelio itself did not expire. Our ability to run it on the small server it was originally installed on, expired. Twice.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply