Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Child XML Destination


Multiple Child XML Destination

Author
Message
Arthur Gary
Arthur Gary
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
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 Run
2009-06-22
9721.55


COLL VAT
STO20090622
Stop Order Receipt Run
2009-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
CozyRoc
CozyRoc
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 2235
Have you seen Keelio's XML Destination Component?

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


Arthur Gary
Arthur Gary
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
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
pranay.laad
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 54
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
rs80
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 379
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
pranay.laad
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 54
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
CozyRoc
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 2235
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
pranay.laad
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 54
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search