Extracting data from non-unique places in XML part 2

[ahogbin]

Back again after the previous question... I am trying to (and struggling in the process to extract two other key pieces of information from within the XML output

<_0:Organisation key="INTERMEDIARY_ORG_001">
        <_0:OrganisationName>
          <_0:TypeCode>CommonName</_0:TypeCode>
          <_0:FullName>AGENT 1 - TEST</_0:FullName>
        </_0:OrganisationName>
      </_0:Organisation>
      <_0:Organisation key="INSURED_ORG_001">
        <_0:MailingAddress addressReference="INSURED_ADDRESS_001"/>
        <_0:AustralianTaxInformation>
          <_0:GSTRegistered>false</_0:GSTRegistered>
        </_0:AustralianTaxInformation>
        <_0:OrganisationName>
          <_0:TypeCode>CommonName</_0:TypeCode>
          <_0:FullName>Allianz Ins Ltd</_0:FullName>
        </_0:OrganisationName>
        <_0:OrganisationName>
          <_0:TypeCode>TradingName</_0:TypeCode>
          <_0:FullName>Allianz Ins Ltd</_0:FullName>
        </_0:OrganisationName>
      </_0:Organisation>

The keys bits I need are AGENT 1 - TEST and Allianz Ins Ltd but the issue I have is that there is no unique xml identifier. I tried to modify your example but so far my efforts have been in vain. The full query I am using is

index=aalalive "Policy Number allocated for Quote" | rex "O [^A-Z]*(?<ENV>[A-Z\-\d+\s]+) \[" | search ENV="PRD*" | eval PRODUCT=substr(Quote_Number, len(Quote_Number)-2,3) | search PRODUCT=COM | map search="search index=stps NEVO policyNumber=$Policy_Number$" maxsearches=10000 | dedup policyNumber | xpath outfield=test "//*[local-name()='AALNet' and *[local-name()='AnnualAmount']]/*[local-name()='EndOfTermAmount']" | xpath outfield=test1 "//*[local-name()='Organisation' and *[local-name()='TypeCode']]/*[local-name()='FullName']" | table policyNumber test1 test

test xpath works perfectly but the test1 is not so successful. What am I missing ?

Cheers.
Alastair

[acharlieh]

Check your XML compared to your xpath closely, and you should see that your issue here is that it's not the Organisation element that has a TypeCode child element but rather the OrganisationName element.

Also I'm guessing you'd also want to consider a filter on the text() of the TypeCode element (maybe even the @key attribute of the Organisation element as well, adding a 3rd layer in.

[acharlieh]

You'll want to spend some time learning xpath and the Document Object Model underneath it as it's pretty involved. There are a number of tutorials online, but a lot of them gloss over xpath and XML Namespaces.

That said... Getting the Full Name, for the Common name of an Organization that has the key of Intermediary_org_001 (in your example AGENT 1 - TEST) I come up with:

| xpath outfield=test "//*[local-name()='Organisation' and @key='INTERMEDIARY_ORG_001']/*[local-name()='OrganisationName' and *[local-name()='TypeCode' and text()='CommonName']]/*[local-name()='FullName']/text()"

But of course depending on what this data means and what you're trying to extract exactly 🙂

[ahogbin]

I feel like I am getting closer.
The following does something (just not what I am expecting). If I understand it correctly the following finds the element 'Organisation' and then checks to see if the element also contains the words INTERMEDIARY_ORG_001

xpath outfield=test "//Organisation[@*[local-name() = 'INTERMEDIARY_ORG_001']/OrganisationName/FullName]"

When I run the query I get the correct number of results but no output (ie just blank lines.

[ahogbin]

A further update..

xpath outfield=test1 "//*[local-name()='OrganisationName' and *[local-name()='TypeCode']]/*[local-name()='FullName']" 

Gives me
Allianz Test Account
Terry
Terry
Now just to work out a way to get the first entry only

[acharlieh]

Hey @ahogbin As you had accepted an answer to your previous question. I moved this comment on an answer out to it's own question first, as while it is similar, it's a new question.