These are the four outputs produced

<ClaimControls />

<ClaimHeader />

<ClaimInfo />

<ClaimDetail />

I need to merge ClaimHeader and ClaimDetail to load into single flat file.

My data in Single record would look like

<Claim>

<ClaimControls>

<Batch_ID />

<Process_Date />

</ClaimControls>

<ClaimHeader>

<Claim_ID />

<Name...

I have a huge nodes in XML. So my SELECT statement goes little long..

My approach : -> XML column to recordset, For Each Recordset-->Dataflow(XML Source from Variable to Flat...

Source : I have one XML column which has data(eg:<BI><Detail><Name /> <DOB /> <Detail/><Control> <Date/></Control></BI>) and 7 other columns holding information like ID, Date, Sytem etc..

Target : Flat File...

🙂

Koen..Thanks. The issue is with the length of the connection string

I have altered the config table design to accomodate lengthy strings. Its working

I did this in Script, which resolved the issue!

Dts.Variables["xmlout"].Value = Dts.Variables["XML"].Value.ToString().Replace("<ROOT>","").Replace("</ROOT>","");

Yes.. Even with the XML generated using FOR XML RAW , "<ROOT></ROOT> " is getting appended when using the query in EXECUTE SQL task with XML resultset

I have already created XML string from table.

I just want to remove the <ROOT></ROOT> tag from my XML string

Its appending ROOT node for that too !!!

I did

Select '<test>testing</test>'

Result set : XML

Result set variable : Test - String datatype

It gave me <ROOT> <test>testing</test></ROOT>

Also I am getting Access Denied when trying to view the above config files even after reverting the changes

Yes 🙂 As you say, i have requested for temp table !

🙁 let me look into the available options;

The Fields from which I need to construct the XML is coming from flat file..

So i need to use them in SCRIPT COMPONENT as destination inside a dataflow and construct...

Koen,

Is it not possible without storing flat file data in Staging table?

Thanks.