using textbetween and replace to parse strings from an xml list

[diwaskarki]

Hello,

I have a field in a db2 table that stores an xml. It looks like this:

In the past , I have used this script to parse strings when there was only one <String></String> tag:

     TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES, chr(10), ''), ' ', ''), '<String>', ''), '</String>', ','),        '<entrykey="Removed Access"><value><List>', '</List>') As MAGroupOwners,

But since , I have multiple <String> </String> tags this time, my script does not bring anything.  I cant find a solution anywhere. I would appreciate any help

here is the sample xml:

<Attributes>
  <Map>
    <entry key="Cause of Removal" value="Associate Activity"/>
    <entry key="Date">
      <value>
        <Long>1521746687446</Long>
      </value>
    </entry>
    <entry key="Removed Access">
      <value>
        <List>
          <String>B2E LDAP Development</String>
          <String>B2E LDAP Development</String>
          <String></String>
          <String>AAI Development | TESTGROUPAAI (GA0401)</String>
        </List>
      </value>
    </entry>
    <entry key="Requestee" value=" (OE17YP)"/>
  </Map>
</Attributes>

Message was edited by: Diwas Karki

[sasiparupudi1]

Please attach the sample xml data in a text file

[MarcoWedel]

What is your expected result based on this sample xml?

[diwaskarki]

Taking a look at the xml:

I want bring in "B2E LDAP Development" As Entitlement

if the data looks like <String>AAI Development | TESTGROUPAAI (GA0401)</String>

I want to bring AAI Development as Application,

TESTGROUPAAI (GA0401) As Entitlment.

[MarcoWedel]

maybe like this?

table1:
LOAD If(Len(Trim(String%Table)),Trim(SubField(String%Table,'|',-1))) as Entitlement,
     If(Index(String%Table,'|'),Trim(SubField(String%Table,'|', 1))) as Application,
     %Key_entry_D6601C760A20EB2F
FROM [QlikCommunity_Thread_299546.xml] (XmlSimple, Table is [Attributes/Map/entry/value/List/String]);
Join
LOAD key,
     value,
     [value/Long] as Long,
     %Key_entry_D6601C760A20EB2F
FROM [QlikCommunity_Thread_299546.xml] (XmlSimple, Table is [Attributes/Map/entry]);

hope this helps

regards

Marco

[diwaskarki]

Hello Marco,

Thanks for replying . Quick question. Could you please explain what %Key_entry_D6601C760A20EB2F is?

Thank you.

[MarcoWedel]

Its an autogenerated key that links the multiple table loads generated by the file wizard.

Please close your thread if your initial question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

[diwaskarki]

Hello Marco,

I think this is the correct answer but I have a few questions before I could gain some results from your answer.

why is the field name String%Table?  Should my field name be "Removed Access"?