Author |
Topic |
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-05-03 : 15:15:59
|
Anybody know how I can export data in XML format from SQL Server 2005?I have a store procedure that returns the XML data in XML format, and I need to pipe it to a file.The SQL Server Business Intelligence Development Studio appears to be useless for such a simple task. What a surprise... |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-05-08 : 12:38:47
|
So, after leaving this on the forum for a week, as well as performing extensive searches over the web, the verdict seems to be that despite SQL Server 2005's professed support for XML data, the ability to export XML has been left out of Integration Services.Heck, it doesn't even support BCP out.Yet another example of Microsoft dropping the ball. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-05-08 : 13:19:46
|
how about you use an old fashiondatareader and put that into a strem that writes it to the file.Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-05-08 : 13:29:39
|
Through Integration Services? Can you give me a start, or a reference? |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-08 : 15:13:28
|
This is from the ms ssis product overview: (http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx)quote: Service Oriented ArchitectureSSIS includes support for sourcing XML data in the data-flow pipeline, including data both from files on disk as well as URLs over HTTP. XML data is “shredded” into tabular data, which then can be easily manipulated in the data flow. This support for XML can work with the support for Web services. SSIS can interact with Web services in the control flow to capture XML data.XML can also be captured from files, from Microsoft Message Queuing (MSMQ), and over the Web via HTTP. SSIS enables the manipulation of the XML with XSLT, XPATH, diff/merge, etc. and can also stream the XML into the data flowThis support enables SSIS to participate in flexible Service Oriented Architectures (SOA).
Quite a few acronyms there.My emphasis, they seem to have realized that "tabular" data is easier to "manipulate"... what a surprise.___________________________________________So much energy to get xml actually working...rockmoose |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-05-08 : 16:26:54
|
Yeah, I came across that text in a MS support document. But it doesn't help me actually create an XML file.I'm not at a 2005 machine right now, but tomorrow I'll check out spirit's datareader suggestion and see if I can get that to work.What pisses me off is that I am at the tail end of my project, and a task that I ASSUMED would take five minutes is taking five days to figure out. How could Microsoft NOT include XML files as a data destination? That just still seems like an amazing oversight to me. |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-08 : 16:44:28
|
Yes, the datareader is pretty straightforward I guess.You could create a CLR procedure that streams the data through a datareader and to a xml file.I have no idea what kind of xml metadata it will want to include. Spirit is the .NET XML guy , maybe he will be around sometime.I can't believe there is no bcp like support either,I mean could you just do a query which formats the data into xml and have a text file as output source.select '<root>' union allselect '<horror><bla>' + ltrim(bla) + '</bla></horror>' from tblunion all select '</root>'this of course presumes that the sp returns the data in tabular-xml-mimic-format.I am not of much help here sorry blindman.Just have to ask, what is the reason for SSIS in this project?rockmoose |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-05-09 : 05:19:14
|
i haven't done any stuff in SSIS yet...i ran it once played for a few hours but that's it.but a preffered way is to output the query as xml into txt file.this seems to be usefull:http://msdn2.microsoft.com/en-US/library/ms140291.aspxbut i have to ask the same question as rocky... why SSIS?with all of the fuss about how great SS2k5 is for XML data thisis a big minus in my book...Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-05-09 : 13:58:46
|
quote: Originally posted by spirit1 but i have to ask the same question as rocky... why SSIS?
Uhmmm...because that is what SSIS is designed for?OK. I'll bite. Why NOT use SSIS? (I mean other than because XML export was omitted from it....) |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-05-09 : 14:04:43
|
well obviously not... since there's no xml destination. well we have a preety complex import for one of our apps that runs on SS2k. DTS wasn't even close to an optionand SSIS isn't much better...i guess it depends on the complexity of the import... so i can't answer why not (other than not havong xml dest support) Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-09 : 14:23:17
|
"Heck, it doesn't even support BCP out"We output XML, admittedly with SQL200, using BCP. We get a stack of "row too wide" type warnings, but it actually works OK.Sorry if totally irrelevant to your scenario!Kristen |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-05-09 : 17:24:11
|
quote: Originally posted by spirit1 well obviously not... since there's no xml destination. well we have a preety complex import for one of our apps that runs on SS2k. DTS wasn't even close to an optionand SSIS isn't much better...i guess it depends on the complexity of the import... so i can't answer why not (other than not havong xml dest support)
I found DTS was good for processing cubes in Analysis Services We also have it running a http job to screen scrape, now that's what I call a robust data import solution I remember the FTP task, you could FTP in, but no way you could use it to FTP out, doh. Maybe that has changed in 2005?rockmoose |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-05-31 : 09:19:28
|
quote: Originally posted by blindmanHeck, it doesn't even support BCP out.Yet another example of Microsoft dropping the ball.
I got this to work a while back:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336I just tested it against a SQL 2005 box and got an error, but it exported fine. And if you're using XML EXPLICIT you don't need to use the root tag hack. |
|
|
|