How to extract multiple attribute name value pair from XML?

[Astro]

Hi,

Our system holds XML logs and the way it is structured, some of values are held inside a common set of name/value attribute pair which repeats number of times within the XML.

Index name is 'applogs'.

Example XML:

<RECORD>
   <ORDER>
      <OrderDate>21-11-2022</OrderDate>
      <OrderRef>12345678></OrderRef>
      <OrderAttributes>
         <OrderAttributeName>Attribute1</OrderAttributeName>
         <OrderAttributeValue>Value1<OrderAttributeValue>
         <OrderAttributeName>Attribute2</OrderAttributeName>
         <OrderAttributeValue>Value2<OrderAttributeValue>
         <OrderAttributeName>Attribute3</OrderAttributeName>
         <OrderAttributeValue>Value3<OrderAttributeValue>
      </OrderAttributes>
   </ORDER>
</RECORD>

I want to extract the individual attributes to display in a table something like this:

OrderDate	OrderRef	Attribute1	Attribute2	Attribute3
21-11-2022	12345678	Value1	Value2	Value3

I have tried SPATH but not able to pull Attribute1 & Value1 pair as there are multiple instances of OrderAttributeName & OrderAttributeValue tags, so have hit the buffers.

Any suggestions on how can I make it work?

[bowesmana · Accepted solution]

You can use spath then foreach to make the attribute/name values to their respective fields.

Note your original XML is malformed - you are missing "/" character on closing OrderAttributeValue and you have an extra > after your OrderRef. 

| makeresults
| eval _raw="<RECORD>
   <ORDER>
      <OrderDate>21-11-2022</OrderDate>
      <OrderRef>12345678</OrderRef>
      <OrderAttributes>
         <OrderAttributeName>Attribute1</OrderAttributeName>
         <OrderAttributeValue>Value1</OrderAttributeValue>
         <OrderAttributeName>Attribute2</OrderAttributeName>
         <OrderAttributeValue>Value2</OrderAttributeValue>
         <OrderAttributeName>Attribute3</OrderAttributeName>
         <OrderAttributeValue>Value3</OrderAttributeValue>
      </OrderAttributes>
   </ORDER>
</RECORD>"

| spath
| rename RECORD.ORDER.* as *
| foreach 0 1 2 3 4  [ eval f=mvindex('OrderAttributes.OrderAttributeName', <<FIELD>>), {f}=mvindex('OrderAttributes.OrderAttributeValue', <<FIELD>>) ]
| table OrderDate OrderRef Attribute*

 The numbers in the foreach will determine how many possible attributes this can handle