How to handle xml?

[Hamder83]

Hi 

I dont work with xml much. (Luckly).

I have a task now, and I tried to phrase it in what seems like a stupid way.

I have tried both importing as xml, and using the xml phraser.

But I don't get all fields.


Anyone know how to do this?

Kind regards

[Yoshiro_Fujimori]

Hi @Hamder83 ,

When xml has multiple layers like yours, it may be necessary to specify the tag explicitly in XML Parse tool.

For example, if you want to collect the data under "Address" tags, you may specify it to "Specific Child Name", and you would get the contents.

Configuration

Workflow

Output (for Address)

I hope this can be of some help.

[Hamder83]

hi @Yoshiro_Fujimori - this is great - this gets me a long way :) 

ive been playing around with this for a few hours now. Do you have an idea, if the list of child fields varies to some extend. How do I automatically get new fields extracted?

kind regards

[Yoshiro_Fujimori · Accepted solution]

Hi @Hamder83 ,

I would say it depends on the complexity of the data structure.

Unlike Relational DB, XML is very flexible in structure.

Simple XML may be able to automatically put into a two dimension table, 

but such a conversion is sometimes difficult for multi-layer XML data, depending on the complexity of the structure.

For example, here is a sample XML data for Customer Transfer exchanged between Financial Institutions.

I think it would not be easy to put it to a table "automatically",

and you would need to design the logic carefully referring to the documentation and the XML schema.

Spoiler

(Source: Message Definition Report Part 1 - SR 2021 (PDF) P91)

<FIToFICstmrCdtTrf>
<GrpHdr>
<MsgId>BBBB/150928-CCT/JPY/123</MsgId>
<CreDtTm>2015-09-28T16:00:00</CreDtTm>
<NbOfTxs>1</NbOfTxs>
<SttlmInf>
<SttlmMtd>COVE</SttlmMtd>
<InstgRmbrsmntAgt>
<FinInstnId>
<BICFI>CCCCJPJT</BICFI>
</FinInstnId>
</InstgRmbrsmntAgt>
<InstdRmbrsmntAgt>
<FinInstnId>
<BICFI>AAAAJPJT</BICFI>
</FinInstnId>
</InstdRmbrsmntAgt>
</SttlmInf>
<InstgAgt>
<FinInstnId>
<BICFI>BBBBUS33</BICFI>
</FinInstnId>
</InstgAgt>
<InstdAgt>
<FinInstnId>
<BICFI>AAAAGB2L</BICFI>
</FinInstnId>
</InstdAgt>
</GrpHdr>
<CdtTrfTxInf>
<PmtId>
<InstrId>BBBB/150928-CCT/JPY/123/1</InstrId>
<EndToEndId>ABC/4562/2015-09-08</EndToEndId>
<TxId>BBBB/150928-CCT/JPY/123/1</TxId>
</PmtId>
<PmtTpInf>
<InstrPrty>NORM</InstrPrty>
</PmtTpInf>
<IntrBkSttlmAmt Ccy="JPY">10000000</IntrBkSttlmAmt>
<IntrBkSttlmDt>2015-09-29</IntrBkSttlmDt>
<ChrgBr>SHAR</ChrgBr>
<Dbtr>
<Nm>ABC Corporation</Nm>
<PstlAdr>
<StrtNm>Times Square</StrtNm>
<BldgNb>7</BldgNb>
<PstCd>NY 10036</PstCd>
<TwnNm>New York</TwnNm>
<Ctry>US</Ctry>
</PstlAdr>
</Dbtr>
<DbtrAcct>
<Id>
<Othr>
<Id>00125574999</Id>
</Othr>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BICFI>BBBBUS33</BICFI>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BICFI>AAAAGB2L</BICFI>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>DEF Electronics</Nm>
<PstlAdr>
<StrtNm>Mark Lane</StrtNm>
<BldgNb>55</BldgNb>
<PstCd>EC3R7NE</PstCd>
<TwnNm>London</TwnNm>
<Ctry>GB</Ctry>
<AdrLine>Corn Exchange 5th Floor</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<Othr>
<Id>23683707994215</Id>
</Othr>
</Id>
</CdtrAcct>
<Purp>
<Cd>GDDS</Cd>
</Purp>
<RmtInf>
<Strd>
<RfrdDocInf>
<Tp>
<CdOrPrtry>
<Cd>CINV</Cd>
</CdOrPrtry>
</Tp>
<Nb>4562</Nb>
<RltdDt>2015-09-08</RltdDt>
</RfrdDocInf>
</Strd>
</RmtInf>
</CdtTrfTxInf>
</FIToFICstmrCdtTrf>