Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am extracting the string in between the string tags from the below xml.
<Attributes>
<Map>
<entry key="Cause of Removal" value="Associate Activity"/>
<entry key="Date">
<value>
<Long>1521745344721</Long>
</value>
</entry>
<entry key="Removed Access">
<value>
<List>
<String>Test New Ent</String>
<String> Test New Ent2</String>
<String>Test New Ent3</String>
</List>
</value>
</entry>
<entry key="Requestee" value=" (OE17YR)"/>
</Map>
</Attributes>
This is the script I am using:
IF (ACTION = 'Remove Role Access', TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES,chr(10),' '),' ', ''), '<String>', ''), '</String>', chr(10)), '<entrykey="RemovedAccess"><value><List>', '</List>')) As [Removed Roles]
However, all the spaces in between the string is replaced with non space. In the script, when I replace the non space with space, it does not work. Script runs but does not bring in anything. Any help would be appreciated. Thanks .
But the problem is
this xml clob is actually stored in a db2 table field.
I am running this query :
Load id
IF (ACTION = 'Remove Role Access', TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES,chr(10),' '),' ', ''), '<String>', ''), '</String>', chr(10)), '<entrykey="RemovedAccess"><value><List>', '</List>')) As [Removed Roles],
IF (ACTION = 'Remove Group Access', TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES,chr(10),' '),' ', ''), '<String>', ''), '</String>', chr(10)), '<entrykey="RemovedAccess"><value><List>', '</List>')) As [Removed Entitlements],
If(INSTANCE='emailSent', STRING1) As AuditEmailSentSubject,
SQL SELECT ID, INSTANCE,SOURCE,ATTRIBUTES
FROM IDENTITYIQ.SPT_AUDIT_EVENT WHERE ACTION='emailSent' or ACTION='Remove Group Access' or ACTION='Remove Role Access'
with ur;
as you can see there are three different instances of action. the data in the string tag changes depending on what the action is.