Dashboards & Visualizations
Highlighted

Extracting data from non-unique places in XML part 2

Communicator

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

Tags (3)
0 Karma
Highlighted

Re: Extracting data from non-unique places in XML part 2

Influencer

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.

0 Karma
Highlighted

Re: Extracting data from non-unique places in XML part 2

Influencer

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.

0 Karma
Highlighted

Re: Extracting data from non-unique places in XML part 2

Communicator

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 INTERMEDIARYORG001

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.

0 Karma
Highlighted

Re: Extracting data from non-unique places in XML part 2

Communicator

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

0 Karma
Highlighted

Re: Extracting data from non-unique places in XML part 2

Influencer

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 Intermediaryorg001 (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 🙂

0 Karma