Extract Table in Event

[RobertRi]

Hi Community!

I have a problem to extract a table in an XML event.

The data looks like this

<data type="info" text="sales:
    VW;1;30.000;
    Bentley;1;70.000;
    Seat;1;15.000;
   Dacia;1;10.000;
   Fiat;1;20.000;
">
<customer>Mr.X</customer>
<time>2020-11-28 16:21:00</time>
</data>

Now I want to have the fields for cartype, quantity and price (VW;1;30.000;).
So that I can summarize the whole sellings from one day.
Could you please help me with that?

Thank you very much!
Rob

[RobertRi]

Thank you very much!

[ITWhisperer]

| spath path="data{@text}" output=sales
| rex field=sales max_match=0 "\n\s+(?<cartype>[^;]+);(?<quantity>\d+);(?<price>[^;]+);"

[RobertRi]

HI ITWhisperer!

Thank you very much for your answer, whith it I have an overview about my data.

Now I have a second question, regarding the prices.
It seems if there are more than 1 matches, the data will be stored like an array in the field.
If I export it to an csv it looks like this:

VW,2,30.000,0000
25.000,0000,

I have tried to round the price to a 2 digit value after the comma, but this does not work.

How can I grab each item in the field and modify it with an eval command?

Regards
Robert

[ITWhisperer]

What does the XML look like in this instance?

[RobertRi]

It's the same like above except that the prices has multiple decimal places after the comma.

<data type="info" text="sales:
    VW;1;30.000;
    Bentley;1;70.000,000000;
    Seat;1;15.000,000000;
   Dacia;1;10.000,000000;
   Fiat;1;20.000,000000;
">
<customer>Mr.X</customer>
<time>2020-11-28 16:21:00</time>
</data>

[ITWhisperer]

| spath path="data{@text}" output=sales
| rex field=sales max_match=0 "\n\s+(?<cartype>[^;]+);(?<quantity>\d+);(?<price>[^;,]+);"

Just add a comma to the exclusion list for the price.