Hey Guys,
I am working on a requirement where I have to extract the value of some nodes in XML which are in a name value pair. Those values are nothing but the products purchased from our website but the XML also contains other elements like discount code, additional products etc and I don't have a way to differentiate between those. I have attached a block of code from my XML which I am really interested in.
Is there a way to extract those values and then display the top products purchased/used from different events. I am interested in the 2 fields productIdentifier and name
<ns3:orderItem>
<ns3:product>
<ns3:productIdentifier>XXXXXXXXXXXXXXX</ns3:productIdentifier>
<ns3:name>XXXXX</ns3:name>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
<ns3:product>
<ns3:productIdentifier>P11845546565263</ns3:productIdentifier>
<ns3:name>Mixit TV (M 2)</ns3:name>
<ns3:instanceIdentifier>A</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
<ns3:product>
<ns3:productIdentifier>P1187877564259</ns3:productIdentifier>
<ns3:name>360 Box</ns3:name>
<ns3:instanceIdentifier>A</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
</ns3:product>
<ns3:product>
<ns3:productIdentifier>P118565565656</ns3:productIdentifier>
<ns3:name>360 Activation omph</ns3:name>
<ns3:instanceIdentifier>A</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
</ns3:product>
</ns3:product>
<ns3:product>
<ns3:productIdentifier>P1068434343545681</ns3:productIdentifier>
<ns3:name>Fibre Broadband</ns3:name>
<ns3:instanceIdentifier>H</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
<ns3:product>
<ns3:productIdentifier>P1046134534341</ns3:productIdentifier>
<ns3:name>Manned Install Only code</ns3:name>
<ns3:instanceIdentifier>H</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
</ns3:product>
<ns3:product>
<ns3:productIdentifier>P1015455566454</ns3:productIdentifier>
<ns3:name>Manned Install Charge</ns3:name>
<ns3:instanceIdentifier>H</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
</ns3:product>
</ns3:product>
<ns3:product>
<ns3:productIdentifier>P1243436565434</ns3:productIdentifier>
<ns3:name>Weekend chatter</ns3:name>
<ns3:instanceIdentifier>I</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
<ns3:directoryServicesRequest>
<ns3:includePhoneNumber>false</ns3:includePhoneNumber>
</ns3:directoryServicesRequest>
<ns3:product>
<ns3:productIdentifier>A1000546567565</ns3:productIdentifier>
<ns3:name>Voicemail Free</ns3:name>
<ns3:instanceIdentifier>I</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
</ns3:product>
<ns3:product>
<ns3:productIdentifier>P10454565656545</ns3:productIdentifier>
<ns3:name>VOC Line Rental</ns3:name>
<ns3:instanceIdentifier>I</ns3:instanceIdentifier>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
</ns3:product>
</ns3:product>
<ns3:product>
<ns3:productIdentifier>D1057845454545</ns3:productIdentifier>
<ns3:name>Free Install - Non QS address</ns3:name>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
</ns3:product>
<ns3:product>
<ns3:productIdentifier>P105704545458</ns3:productIdentifier>
<ns3:name>Install Activation Fee</ns3:name>
<ns3:action>New</ns3:action>
<ns3:quantity>1</ns3:quantity>
</ns3:product>
</ns3:product>
</ns3:orderItem>
Let me know if anyone has worked on this type of requirement before and if they can be of any help.
Best Regards,
SA
You could try something like this
| rex max_match=0 "(?ms)<(\w+:)?productIdentifier>(?<productIdentifier>[^<]+)</(\w+:)?productIdentifier>.*?<(\w+:)?name>(?<productName>[^<]+)</(\w+:)?name>"
| eval idName=mvzip(productIdentifier,productName)
| stats count by idName
| eval productIdentifier=mvindex(split(idName,","),0)
| eval productName=mvindex(split(idName,","),1)