Extraction of Response values from XML in the desired way.

[yamini_37]

Hi All,

I am trying to extract the multiple response values for a single request. After using "spath" command, the result is shown below.
Here, I didn't extract any values and just tabulated the needed information.

So, in the XML file, ALB city has 4 streets. Each street contains 4 members. This is single result . Now I want to break it as 16 results with all details as shown below.

My query looks like
index=ab host=xy source=*y.xml |spath |table _time host City_Name Street_name Person_code Person_role

The above query gave the results shown in the first pic. Now i want to get the results as shown in the second picture. Please help me in framing the query for the latter part to get the expected results.

Thank You.

[to4kawa]

| makeresults
| eval City_name="ALB"
| eval Street_name=split("Street 1,Street 2,Street 3,Street 4",",")
| eval Person_code=split("1,2,3,4",",")
| eval Person_role=split("Father,Mother,Brother,Sister,Father,Mother,Brother,Sister,Father,Mother,Brother,Sister,Father,Mother,Brother,Sister",",")
| table City_name Street_name Person_code Person_role
| rename COMMENT as "this is your sample. please check this. from here, the logic"

| eval Person_role=mvdedup(Person_role)
| eval Person=mvzip(Person_code,Person_role)
| mvexpand Street_name
| stats values(City_name) as City_name list(Street_name) as Street_name by Person
| mvexpand Street_name
| eval Person_code=mvindex(split(Person,","),0), Person_role=mvindex(split(Person,","),1)
| fields - Person
| table City_name Street_name Person_code Person_role
| sort Street_name

[to4kawa]

JSON:

| makeresults
| eval _raw="{\"City Name\":\"ALB\",\"Street name\":[\"street 1\",\"street 2\",\"street 3\",\"street 4\"],\"Person code\":[1,2,3,4],\"Person role\":[\"Father\",\"Mother\",\"Brother\",\"Sister\",\"Father\",\"Mother\",\"Brother\",\"Sister\",\"Father\",\"Mother\",\"Brother\",\"Sister\",\"Father\",\"Mother\",\"Brother\",\"Sister\"]}"
| spath

what's XML ?

[vnravikumar]

Hi

Can you post sample XML?

[yamini_37]

I couldn't able to share the xml here

[yamini_37]

please find below.

[vnravikumar]

Hi

Still, it is missing

[yamini_37]

I have posted a new question and tagged you. Could you please check. The subject line of the question is "challenge!!! extraction of XML response values"

[vnravikumar]

I not able to find, can you post the URL?

[yamini_37]

HI Ravi,

Please find the sample xml. I want to map the data and display as shown in the second picture.