You need to put the root path at "one of" the deepest nesting and have the xpath for other columns relative to that. You can not traverse multiple nestings in a single transformation. For example:
Customer - Name - Addresses - Address - Address - Orders - Order - Order
From above you can either choose to traverse Customer's Addresses OR Orders in a single transformation, not both. You are free to define two different transformations (views) one for each and later combine both tables based on some common element/attribute (like PK). From your example, you need to do like
XMLTABLE('/response/hits/hists/_source/segments/PhoneNumbers' PASSING JSONTOXML('response', f.result COLUMNS customernumber string PATH ../customernumber/text(), number string, citycode string ...)
Should do the trick. The same applies for your next question.
Thank you for the quick response Ramesh. Earlier i was successful importing multiple columns from single nested loop and was able deploy the VDB and data is available through JDBC and Rest API. I wanted to know if there is a way i can traverse multiple columns from multiple nestings in a single transformation as we have more than 30 nestings and performance will be impacted if we create 30 transformations and join them later. But based on your response above its clear that we can not traverse multiple columns from multiple nestings in a single transformation. I will check if there is a way we can flatten the json file or minimize the nested loops.
XMLTABLE does support retrieving a sequence as an array type as well. For example if there is an id associated with an address / order you could pull those as an array.