How do you extract fields from an XML file?

[varmamkm]

sample data

<MAINNODE>
     <NODE1 ID="C1" DATE="2018-11-16 09:20:01">
        <NODE2>
            <NODE3 CODE="A1" AMOUNT="100"/>
            <NODE3 CODE="A2" AMOUNT="200"/>
            <NODE3 CODE="A3" AMOUNT="300"/>
        </NODE2>
    </NODE1>
    <NODE1 ID="C2" DATE="2018-11-16 09:20:01">
        <NODE2>
            <NODE3 CODE="A2" AMOUNT="100"/>
            <NODE3 CODE="A1" AMOUNT="200"/>
        </NODE2>
    </NODE1>
 </MAINNODE>

Can you please help me out to build a regular expression or any other method (ex: xpath) to get the rows as mentioned below.

ID="C1", DATE="2018-11-16 09:20:01", CODE="A1", AMOUNT="100"
ID="C1", DATE="2018-11-16 09:20:01", CODE="A2", AMOUNT="200"
ID="C1", DATE="2018-11-16 09:20:01", CODE="A3", AMOUNT="300"
ID="C2", DATE="2018-11-16 09:20:01", CODE="A2", AMOUNT="100"
ID="C2", DATE="2018-11-16 09:20:01", CODE="A1", AMOUNT="200"

[akocak · Accepted solution]

You can obviously do all sort of things during index-time. However, if search time, this app probably has everything you need:

https://splunkbase.splunk.com/app/455/

if you had this app, your search would look like
index="xmlfile" | xmlkv

if you need nested xml to be extracted,
index="xmlfile" |xmlkvrecursive

as mentioned, spath or rex would work on this task too.

[richgalloway]

Why does it have to be a regular expression? Have you considered the xpath command?

[varmamkm]

Thanks! i have solved this by using spath.. can you please validate the query below

index="xmlfile" | spath output=NODE3CODE path=NODE1.NODE2.NODE3{@CODE} | spath output=NODE3AMOUNT path=NODE1.NODE2.NODE3{@AMOUNT} | eval x=mvzip(NODE3CODE, NODE3AMOUNT) | mvexpand x | eval x = split(x,",") | eval NODE3CODE=mvindex(x,0) | eval NODE3AMOUNT=mvindex(x,1) | table DATE ID NODE3CODE NODE3AMOUNT