Multiple Child XML Destination

Question

Post reply

Multiple Child XML Destination

Arthur Gary

SSC Veteran

Points: 249
More actions
June 23, 2009 at 9:04 am

#211193

Hello All,
I'm having trouble trying to transform data from a basic flat file to a multiple level XML destination.
I have basic Pipe delimited flat file in this format
contract_obj|batch_reference|transaction_narration|due_date|amount
COLL COM|STO20090622|Stop Order Receipt Run|20090622|000009721.550000000
COLL VAT|STO20090622|Stop Order Receipt Run|20090622|000001361.020000000
I need to produce (Segment of XML):
COLL COM
STO20090622
Stop Order Receipt Run2009-06-22
9721.55
COLL VAT
STO20090622
Stop Order Receipt Run2009-06-22
1361.02
Any ideas?
Can SSIS do this? I'm currently using another ETL tool that can do this, but it's heavy on resources and an adminitration nightmare.
Regards,
Gary

Viewing 8 posts - 1 through 7 (of 7 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Have you seen Keelio's XML Destination Component?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Arthur Gary SSC Veteran Points: 249 More actions · Answer 2

Hello Cozy Roc,

Thanks for the advice, yes I have seen that destination. This doesn't serve the XML multi-level completely. I do believe that it should be native to SQL SSIS.

However, I have installed the trial version of the SSIS XML kit from Keelio and I'm going to do tests on it. I'll update this log.

Thanks.

pranay.laad Valued Member Points: 51 More actions · Answer 3

Hi Gary,

Did you try Keelio software. What is your feedback? I need to generate XML from multiple elements sourced from file,database etc. and write multiple nodes, parent child relations to XML, How this is possible? Any ideas?

rs80 SSCrazy Points: 2312 More actions · Answer 4

From what I know, SSIS doesn't have an inherit way to do that.

When I had to create a nested XML file I did that in the stored proc and then used ssis to create the xml file. If you want more info on how to do that I could dig it up.

pranay.laad Valued Member Points: 51 More actions · Answer 5

Hi rs80 and All,

The idea is use to SSIS to generate complex XML files with multiple elements in it, hierarchical. It is also must to validate this XML against XSD. I know other ETL tools have this capability but we need to use SSIS for this purpose. Please let me know. If SSIS is not capable what scripting, tools or SQL Server or what we can use to achieve this requirement? Whatever we use the component should be generic for all the ETL jobs so that multiple XML files can be generated reusing the functionality. Are tools like CozyRoc, Keelio capable of doing this? Can they accept multiple input streams and compose complex XML's?

Please share your inputs.

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

Hi Pranay,

COZYROC doesn't provide XML Destination component at this time. We provide Template Task, which can be used to generate arbitrary text documents including XML. You can generate documents with any complexity and it does support multiple streams of data. You have setup the document layout (template) using the syntax of the open-source Apache Velocity templating engine. The process is similar to the creation ASP page. I would recommend you review the samples.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

pranay.laad Valued Member Points: 51 More actions · Answer 7

Yes, but how could we validate the XML output with a predefined XSD. I don't think this is optimal solution to the requirement. Please suggest.