Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Export XML file?

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.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-08 : 13:19:46
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: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

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?
Go to Top of Page

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 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
Go to Top of Page

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.
Go to Top of Page

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

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.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: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

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....)
Go to Top of Page

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 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: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

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
Go to Top of Page

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 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
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-31 : 09:19:28
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
   

- Advertisement -