Export data into xml file using SSIS

Question

Post reply

Export data into xml file using SSIS

Girish Dindukurthy

SSC Rookie

Points: 39
More actions
February 1, 2010 at 3:59 am

#216224

any one have idea on how to Export data into xml file from sql server 2005 using SSIS
please provide me some samples if you have any

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

wschampheleer SSCertifiable Points: 5504 More actions · Answer 1

Check the following articles:

Jamie Thomson

Simon Sabin

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

A O Grasshopper Points: 23 More actions · Answer 2

Is there a way to output the XML to a more readable format

jaydavisus Valued Member Points: 68 More actions · Answer 3

To make your XML more readable, use xlst to transform it. For more info try: http://www.w3schools.com/xsl/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog

A O Grasshopper Points: 23 More actions · Answer 4

Thanks for the replay. Sorry I wasn't specific enough. What I want is xml to xml. Example the output from SQL is single row like this:

<?xml version="1.0" encoding="ISO-8859-1"?><catalog><cd><title>Empire Burlesque</title><artist>Bob Dylan</artist><country>USA</country><company>Columbia</company><price>10.90</price><year>1985</year></cd></catalog>

How can I output it to this:

<?xml version="1.0" encoding="ISO-8859-1"?>

<cd>

<title>Empire Burlesque</title>

<artist>Bob Dylan</artist>

<company>Columbia</company>

</cd>

</catalog>

Any help is appreciated.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Realize I must be getting old and the eyes wearing out..... what is the difference between the two xml fragments you've supplied? If it's purely formatting, that's extraneous to XML and makes no difference (other than to carbon-based readers).

If you want to see/view it formatted, simply save off the file and view it in Internet Explorer or even SQL Server Management Studio (CTRL-K-D will format the document).

Steve.

Sena Ramsey SSC Veteran Points: 260 More actions · Answer 6

Steve, I'm having a simular problem with exporting to an xml file using bcp. I'm sure you mean people when you reference "carbon-based readers" --- but if you can't look at the output using IE, how do you know that other programs would have a problem reading the single line out!

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 7

Not sure I follow your train of thought... Yes, people like to read XML when it's formatted but there is no requirement for it to be formatted. Like i said in the earlier post, if you really really want to see it formatted, open the file in either IE or SSMS (then requires you to request SSMS to format the document).

Steve.

Sena Ramsey SSC Veteran Points: 260 More actions · Answer 8

Steve, this is my first time usings bcp to create a XML file 🙂 I don't really care if I can read it, I just want to make sure the receiving application can read it. When I create the file I get a style sheet error when I try and view it using IE. What do I need to have in the file to insure any application can read it?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 9

Can you post the error? Without seeing that it's very hard to determine what might be missing/needed. If you could also post a small part of the bcp call/s you're making that may help also.

Out of interest, was SSMS able to open the file and format it?

Steve.

Sena Ramsey SSC Veteran Points: 260 More actions · Answer 10

Steve -- thanks for your help!

Yes - SSMS can format it using Ctrl K D

With IE I receive the following error:

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

Only one top level element is allowed in an XML document. Error processing resource 'file:///C:/temp/SampleXMLOutput.xml'. ...

<Job><Site>ADN</Site><JobId>025455</JobId><JobType>Print</JobType><WorkOrder>...

Thanks

Sena

LutzM SSC Guru Points: 107049 More actions · Answer 11

Would you mind sharing a sample file that shows the problem you're stuck with?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 12

Great idea Lutz, would likely help out.

While you're looking at posting that file, I think you'll find that the problem is that the xml generated is in the form of

NodeA_1

OtherNodes

CloseNodeA_1

NodeA_2

OtherNodes

CloseNodeA_2

.....

NodeA_n

OtherNodes

CloseNodeA_n

I would consider this an xml fragment verus a document. IE (and likely other apps) require the document layout, which for the same data example simply needs a root node, like...

RootNodeX

NodeA_1

OtherNodes

CloseNodeA_1

NodeA_2

OtherNodes

CloseNodeA_2

.....

NodeA_n

OtherNodes

CloseNodeA_n

CloseRootNodeX

To get this root node, you need to specify a value for the ROOT option/parameter in the FOR XML clause.

HTH,

Steve.

Sena Ramsey SSC Veteran Points: 260 More actions · Answer 13

Sena Ramsey

SSC Veteran

Points: 260

May 17, 2010 at 11:47 am

#1168383

Steve -- thanks that was it! Needed the ROOT:-)

Sena Ramsey SSC Veteran Points: 260 More actions · Answer 14

Lutz, thanks for your help -- You and Steve both were a great help!