XML Stage to convert child repeating elements into rows

[partha.natta]

Hi All,

Below its a sample transaction record that I am trying to process in XML stage.
There can be multiple TransferFund under one traansaction, when more than one TransferFund are coming then the detila inside the trasnfer fund should come in a new row with the Parent Tag Values.

Could you please lert me know how to solve this issue.

<TrxnRec><ProcessDate>20140702</ProcessDate>
<FundAcctID>26204388</FundAcctID>
<AcctDesig>1</AcctDesig>
<DlrCode>9190</DlrCode>
<DlrAcctID>543080691</DlrAcctID>
<RepCode>AAA</RepCode>
<AcctType>01</AcctType>
<OrdSrc>D</OrdSrc>
<OrdType>D</OrdType>
<SrcID>ETCOF0262043889</SrcID>
<ETCof>
<TrxnTyp>5</TrxnTyp>
<TrxnTypDtl>4</TrxnTypDtl>
<FromAcct>
<FundAcctID>26204388</FundAcctID>
<DlrCode>7568</DlrCode>
<DlrAcctID>543080691</DlrAcctID>
<AcctDesig>1</AcctDesig>
<AcctType>01</AcctType>
</FromAcct>
<TrnsfrFund>
<FundID>266</FundID>
<GrossAmt>0.00</GrossAmt>
<UnitTrxnd>0.0000</UnitTrxnd>
<AveCost>0.0000</AveCost>
</TrnsfrFund>
<TrnsfrFund>
<FundID>568</FundID>
<GrossAmt>21180.08</GrossAmt>
<UnitTrxnd>1650.7480</UnitTrxnd>
<AveCost>0.0000</AveCost>
</TrnsfrFund>
<TrnsfrFund>
<FundID>590</FundID>
<GrossAmt>11394.67</GrossAmt>
<UnitTrxnd>574.8760</UnitTrxnd>
<AveCost>0.0000</AveCost>
</TrnsfrFund>
<TrnsfrFund>
<FundID>702</FundID>
<GrossAmt>26314.79</GrossAmt>
<UnitTrxnd>1326.8990</UnitTrxnd>
<AveCost>0.0000</AveCost>
</TrnsfrFund>
<TradeDate>20140702</TradeDate>
<SettlDate>20140702</SettlDate>
<Demo>
</Demo>
</ETCof>
</TrxnRec>
</TrxnRecon>

[ray.wurlod]

How about you describe to us what the actual issue is? What is it that you are trying to accomplish? For example are you looking to read or to generate XML of this kind?

[partha.natta]

Hi Ray,

Thanks for your response. Yes, I am trying to read this type of XML file and want to create multiple rows based on the number of TransferFund sections coming in the XML file.

[MT]

Hi partha.natta


use the Parser step within the XML stage.
The PARSER defines how the XML looks like
and in the OUTPUT step you defines how many rows will be created.

[partha.natta]

Hi Michael,

I am doing the same steps in my job, and but I am getting only the first TransfrID values(FundID,GrossAmt,UnitTrxnd,AveCost) coming in output.For the other TransferID details should come in a different row in same column (FundID,GrossAmt,UnitTrxnd and AveCost) with the parent values i.e. extra new row need to be created.

[partha.natta]

The output result should come like this.

Expected Result:

ProcessDate|FundAcctID|DlrAcctID|RepCode|FundID|GrossAmt|UnitTrxnd|AveCost
20140702|26204388|543080691|AAA|266|0|0|0
20140702|26204388|543080691|AAA|568|21180.08|1650.748|0
20140702|26204388|543080691|AAA|590|11394.67|574.876|0
20140702|26204388|543080691|AAA|702|26314.79|1326.899|0

But currently I am getting only the first record.

[eostic]

That often indicates that you either mapped to the wrong list, or that your "list" isn't correctly defined. Be sure that in your xsd, the node that you want multiple rows has a proper "maxOccurs=unbounded" or some number greater than 1.

It must be formally stated...the default for xsd's is maxOccurs=1.....

The xml Stage obeys the xsd standard.

Ernie

[partha.natta]

Hi Ernie,

Thanks for your response.

This way it is mentioned in XSD.

<xsd:element name="ETCof">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="TrxnTyp"/>
<xsd:element ref="TrxnTypDtl"/>
<xsd:element ref="CntbWdCode" minOccurs="0"/>
<xsd:element ref="OrdFundID" minOccurs="0"/>
<xsd:choice>
<xsd:element ref="ToAcct"/>
<xsd:element ref="FromAcct"/>
</xsd:choice>
<xsd:choice maxOccurs="unbounded">
<xsd:element ref="TrnsfrAAFund"/>
<xsd:element ref="TrnsfrFund"/>
</xsd:choice>
<xsd:element ref="TaxEvnt" minOccurs="0"/>
<xsd:element ref="OrigNtwrk" minOccurs="0"/>
<xsd:element ref="TradeDate"/>
<xsd:element ref="SettlDate"/>
<xsd:element ref="Demo" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>