SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Export XML file?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 05/03/2006 :  15:15:59  Show Profile
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 05/08/2006 :  12:38:47  Show Profile
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 05/08/2006 :  13:19:46  Show Profile  Visit spirit1's Homepage
how about you use an old fashion
datareader and put that into a strem that writes it to the file.

Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 05/08/2006 :  13:29:39  Show Profile
Through Integration Services? Can you give me a start, or a reference?
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 05/08/2006 :  15:13:28  Show Profile
This is from the ms ssis product overview: (http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx)
quote:
Service Oriented Architecture

SSIS 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 flow

This 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

Edited by - rockmoose on 05/08/2006 15:14:36
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 05/08/2006 :  16:26:54  Show Profile
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.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 05/08/2006 :  16:44:28  Show Profile
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 all
select '<horror><bla>' + ltrim(bla) + '</bla></horror>' from tbl
union 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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 05/09/2006 :  05:19:14  Show Profile  Visit spirit1's Homepage
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.aspx

but i have to ask the same question as rocky... why SSIS?

with all of the fuss about how great SS2k5 is for XML data this
is a big minus in my book...

Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp

Edited by - spirit1 on 05/09/2006 05:25:59
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 05/09/2006 :  13:58:46  Show Profile
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....)
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 05/09/2006 :  14:04:43  Show Profile  Visit spirit1's Homepage
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 option
and 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: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/09/2006 :  14:23:17  Show Profile
"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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 05/09/2006 :  17:24:11  Show Profile
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 option
and 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

Edited by - rockmoose on 05/09/2006 17:25:27
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 05/31/2006 :  09:19:28  Show Profile  Visit robvolk's Homepage
quote:
Originally posted by blindman
Heck, 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=9336

I 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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000