what am I doing wrong?

[diwaskarki]

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 

[jonathandienst]

Are you reading the data as a text file, or an XML file? I suggest that you upload the complete load statement(s) as well as where/how ACTION and ATTRIBUTES (fields or variables?) are set.

[diwaskarki]

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.