Multiple Child XML Destination

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Multiple Child XML Destination

Multiple Child XML Destination

Rate Topic

Display Mode

Topic Options

Author

Message

Arthur Gary

Posted Tuesday, June 23, 2009 9:04 AM

Forum 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

CozyRoc

Posted Wednesday, June 24, 2009 5:49 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, June 09, 2013 7:39 AM
Points: 1,106, Visits: 2,120

Have you seen Keelio's XML Destination Component?

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

Post #740933

Arthur Gary

Posted Wednesday, June 24, 2009 6:57 AM

Forum 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

pranay.laad

Posted Friday, March 29, 2013 8:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 3:16 AM
Points: 3, Visits: 33

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

rs80

Posted Friday, April 05, 2013 3:25 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 31, 2013 8:27 AM
Points: 72, Visits: 224

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

pranay.laad

Posted Saturday, April 06, 2013 3:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 3:16 AM
Points: 3, Visits: 33

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

CozyRoc

Posted Saturday, April 06, 2013 6:24 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, June 09, 2013 7:39 AM
Points: 1,106, Visits: 2,120

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

pranay.laad

Posted Saturday, April 06, 2013 9:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 3:16 AM
Points: 3, Visits: 33

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 »

Permissions