How to extract fields from xml in a lookup table?

[matstap]

I have a lookup table where one of the field columns is xml format. I'm trying to extract fields from the xml entries, but no results come up for all my attempts. Here is my code:

| inputlookup file.csv 
| xpath outfield=OfferingID field=tdrxml "Offering/Comments/ul/li" 
| xpath outfield=TDR field=tdrxml "Offering/TDR/@name" 
| xpath outfield=Type field=tdrxml "Offering/TDR/@type" 
| table OfferingID TDR Type 

[niketn · Accepted solution]

@matstap, please try the following to get all XML path extracted using spath:

| inputlookup file.csv 
| rename tdrxml=_raw
| spath
| rename "Offering.Comments.ul.li" as OfferingID
| rename "Offering.TDR{@name}" as TDR 
| rename "Offering.TDR{@type}"  as Type
| table OfferingID TDR Type

Following is a run anywhere search based on your data..

| makeresults 
| eval tdrxml=" <?xml version=\"1.0\" ?>
 <!DOCTYPE .....>

     <!--#####################################################################-->

     <!--#####################################################################-->

 <Offering name=\"name\">
     <Comments> 
         <ul>
              <li>offeringID = 29  </li>
         </ul>
     </Comments>
 <!-- ############################################################################################## -->

     <TDR name=\"name1\" type=\"3\" version=\"1\">
         <Comments> 
             words words words
         </Comments>

         <Field name=\"fielda\" type=\"oid\">
             <Comments> 
                 words words words
             </Comments>
         </Field>
         <Field name=\"fieldb\" type=\"string\" length=\"12\">
             <Comments> 
                 words words words
             </Comments>
         </Field>
     </TDR>

 <!-- ############################################################################################## -->

     <TDR name=\"name2\" type=\"3\" version=\"1\">
         <Comments> 
             words words words
         </Comments>

         <Field name=\"fieldc\" type=\"oid\">
             <Comments> 
                 words words words
             </Comments>
         </Field>
         <Field name=\"fieldd\" type=\"string\" length=\"12\">
             <Comments> 
                 words words words
             </Comments>
         </Field>
     </TDR>

 </Offering>" 
| rename tdrxml as _raw 
| spath 
| rename "Offering.Comments.ul.li" as OfferingID 
| rename "Offering.TDR{@name}" as TDR 
| rename "Offering.TDR{@type}" as Type 
| table OfferingID TDR Type

[mayurr98]

Try this

| inputlookup file.csv 
 | rex field=tdrxml “offeringID\s=\s(?<offeringID>\d+)”
 | rex field=tdrxml max_match=0 “TDR\sname=\"(?<TDR>[^\"]+).*type=\"(?<Type>[^\"]+)”
 | table OfferingID TDR Type 

Let me know if this helps!

[p_gurav]

Can you provide some sample data?

[matstap]

<?xml version="1.0" ?>
<!DOCTYPE .....>

    <!--#####################################################################-->

    <!--#####################################################################-->

<Offering name="name">
    <Comments> 
        <ul>
             <li>offeringID = 29  </li>
        </ul>
    </Comments>
<!-- ############################################################################################## -->

    <TDR name="name1" type="3" version="1">
        <Comments> 
            words words words
        </Comments>

        <Field name="fielda" type="oid">
            <Comments> 
                words words words
            </Comments>
        </Field>
        <Field name="fieldb" type="string" length="12">
            <Comments> 
                words words words
            </Comments>
        </Field>
    </TDR>

<!-- ############################################################################################## -->

    <TDR name="name2" type="3" version="1">
        <Comments> 
            words words words
        </Comments>

        <Field name="fieldc" type="oid">
            <Comments> 
                words words words
            </Comments>
        </Field>
        <Field name="fieldd" type="string" length="12">
            <Comments> 
                words words words
            </Comments>
        </Field>
    </TDR>

</Offering>