Pulling in data to excel from XML messages

[mccrimmon]

Hello

Im hoping someone can help point me in the right direction and start me off on this one.

I have a folder of XML messages and I would like to bring these XML messages into Excel and create a summary report.

I am wondering how you work with XML messages in Excel and how I only pull in certain fields as the messages contain a lot of information which is irrelevant.

I have attached a copy of a dummy XML message and also attached an example output.

From the example you will see I am interested in only pulling in the following tags from the XML message.

• Status
• DealRef
• TradeRef
• Qty

Any pointers would be greatly appreciated.

I am really only interested in how you cut the XML message up at this point as Im sure I can work out how to loop through the folder etc.

Thanks

McCrimmon

[snb]

Please Login or Register  to view this content.

[TMS]

Does the attached example help ....



Regards

[mccrimmon]

Please Login or Register  to view this content.

fantastic work snb.

[mccrimmon]

A further question snb.

Should I want to bring in further fields I obviously expand the array and add in the additional start and end tags, however, is it possible to put wild cards in the start tags.


For example, the below will always start as <NetSttlmAmt ccy=
However, could have any other currency such as GBP, USD etc before closing out the tag.

<NetSttlmAmt ccy="EUR">1051698.6</NetSttlmAmt>

Is there away so that I can have it so its using a wild card on the 3 digit currency code?
<NetSttlmAmt ccy="*">

Thanks

McC

[snb]

No you can't, and in this case it's an advantage:

Please Login or Register  to view this content.

results in "<NetSttlmAmt ccy="EUR">1051698.6"

Please Login or Register  to view this content.

results in ""EUR">1051698.6"

if you adjust the resulting string c01 using

Please Login or Register  to view this content.

""EUR">1051698.6" will result in:

EUR 1051698.6

I would be very pleased with this result.

[mccrimmon]

No you can't, and in this case it's an advantage:

Please Login or Register  to view this content.

results in "<NetSttlmAmt ccy="EUR">1051698.6"

Please Login or Register  to view this content.

results in ""EUR">1051698.6"

if you adjust the resulting string c01 using

Please Login or Register  to view this content.

""EUR">1051698.6" will result in:

EUR 1051698.6

I would be very pleased with this result.

Thanks for the reply.
I'll take a look.

Rep added for this.