Aligning Multivalue field from XML nodes with multiple attributes

[RedHeron]

Hi, I'm trying to create a table as below:

method	lat	lon
blue	3578114	4960035
red
green
yellow	3578113	4960032

I tried using split  but I don't get the correct order as shown below. 

method	lat	lon
blue red green yellow	3578113 3578114	4960032 4960035

An excerpt of the the xml is below  and I'm able to extract the correct  order if I use xpath but sometimes the xml source file has  extra data at the end which prevent xpath from reading the data.  

Is there a way to read the "method" elements  below besides using xpath such as 

| xpath outfield=lat_blue "//response_data/position_data/PositioningMethodAndUsage[@method='blue']/position_estimate/pointWithAltitudeAndUncertaintyEllipsoid/geographicalCoordinates/lat"

I want to bullet proof this in case the xml file is broken.

<file>
<reference_id>12345678</reference_id>
<session_id>1256555</session_id>
<positioning_request_time utc_off="-0800">19800228082202</positioning_request_time>
<network type="iden"></network>
<response_data type="Success">
<position_data>
<PositioningMethodAndUsage method="blue" locationReturn="NO">
<positionresultCode>99</positionresultCode>
<timeStamp utc_off="-0800">20220228082203</timeStamp>
</PositioningMethodAndUsage>
<PositioningMethodAndUsage method="red" locationReturn="NO">
<positionresultCode>99</positionresultCode>
<timeStamp utc_off="-0800">20220228082203</timeStamp>
</PositioningMethodAndUsage>
<PositioningMethodAndUsage method="green" sourceOfAltitude="3D" locationReturn="YES">
<positionresultCode>1</positionresultCode>
<position_estimate>
<pointWithAltitudeAndUncertaintyEllipsoid>
<geographicalCoordinates>
<latSign type="North"></latSign>
<lat>3878113</lat>
<lon>-4360032</lon>
</geographicalCoordinates>
<altitudeAndDirection>
<directionOfAltitude>height</directionOfAltitude>
<altitude>232</altitude>
</altitudeAndDirection>
</pointWithAltitudeAndUncertaintyEllipsoid>
</position_estimate>
</PositioningMethodAndUsage>
<PositioningMethodAndUsage method="yellow" locationReturn="NO">
<positionresultCode>1</positionresultCode>
<position_estimate>
<pointWithAltitudeAndUncertaintyEllipsoid>
<geographicalCoordinates>
<latSign type="North"></latSign>
<lat>3878114</lat>
<lon>-4360035</lon>

</PositioningMethodAndUsage>

</response_data>
</file>

[ITWhisperer · Accepted solution]

You could try extracting each element with rex and then mvexpand

| rex max_match=0 "(?ms)(?<positiondata>\<PositioningMethodAndUsage.+?\</PositioningMethodAndUsage\>)"
| mvexpand positiondata