Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Child XML Destination Expand / Collapse
Author
Message
Posted Tuesday, June 23, 2009 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 29, 2013 9:32 AM
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):
<?xml version="1.0" encoding="ISO-8859-1"?>
<Extract >
<Data>
<OLTPTable NAME="Transaction">
<Record>
<contract_obj>COLL COM</contract_obj>
<batch_reference>STO20090622</batch_reference>
<transaction_narration>Stop Order Receipt Run</transaction_narration>
<due_date>2009-06-22</due_date>
<amount>9721.55</amount>
</Record>
<Record>
<contract_obj>COLL VAT</contract_obj>
<batch_reference>STO20090622</batch_reference>
<transaction_narration>Stop Order Receipt Run</transaction_narration>
<due_date>2009-06-22</due_date>
<amount>1361.02</amount>
</Record>
</OLTPTable>
</Data>
</Extract>


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
Post #740276
Posted Wednesday, June 24, 2009 5:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:22 AM
Points: 1,114, Visits: 2,190
Have you seen Keelio's XML Destination Component?

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

Post #740933
Posted Wednesday, June 24, 2009 6:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 29, 2013 9:32 AM
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.
Post #740996
Posted Friday, March 29, 2013 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:06 AM
Points: 3, Visits: 48
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?
Post #1436960
Posted Friday, April 05, 2013 3:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:26 PM
Points: 97, Visits: 289
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.
Post #1439504
Posted Saturday, April 06, 2013 3:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:06 AM
Points: 3, Visits: 48
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.
Post #1439555
Posted Saturday, April 06, 2013 6:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:22 AM
Points: 1,114, Visits: 2,190
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/

Post #1439579
Posted Saturday, April 06, 2013 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:06 AM
Points: 3, Visits: 48
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.
Post #1439589
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse