Issue in query to read Attribute_Meta_Data

IdentityIQ (IIQ) IIQ Discussion and Questions
,
1

Which IIQ version are you inquiring about?

Version 8.3

Share all details related to your problem, including any error messages you may have received.

Hi,

I have identities where users may have any of the 4 attribute metadata

  1. <AttributeMetaData attribute="email" source="MailTFAYD:mail"/>
  2. <AttributeMetaData attribute="email" source="Application_HR:C001_EMAIL"/>
  3. <AttributeMetaData attribute="email" source="SAP Fieldglass Worker:Worker_Email"/>
<AttributeMetaData attribute="email" modified="1573068729516" user="206532010">
	<LastValue>
		<String>[email protected]</String>
	</LastValue>
   </AttributeMetaData>
  1. Some users may not have this attribute metadata.

For first option I have below query which returns all identities with source=“MailTFAYD:mail”:

SELECT NAME, EMAIL_SOURCE FROM (
	SELECT  
		NAME NAME, xmltype(ATTRIBUTE_META_DATA).extract('/List/AttributeMetaData[@attribute="email"]/@source').getStringVal() AS EMAIL_SOURCE  
	FROM spt_identity 
	WHERE  
		XMLEXISTS('/List/AttributeMetaData[@attribute="email" and @source="MailTFAYD:mail"]' PASSING XMLType(ATTRIBUTE_META_DATA)) 
		AND ATTRIBUTE_META_DATA IS NOT NULL 
		AND length(ATTRIBUTE_META_DATA) > 1 
) 
WHERE EMAIL_SOURCE IS NOT NULL 
AND length(EMAIL_SOURCE) > 1;

Now I need query to find all the identites where attribute meta data is of type 4, and need NAME, “user” and “LastValue”.

Any help will be highly appriciated.

2

SELECT NAME, EMAIL_SOURCE
FROM (
SELECT
NAME AS NAME,
xmltype(ATTRIBUTE_META_DATA).extract(‘/AttributeMetaData[@attribute=“email”]/LastValue/String’).getStringVal() AS EMAIL_SOURCE
FROM spt_identity
WHERE
XMLEXISTS(‘/AttributeMetaData[@attribute=“email”]’ PASSING XMLType(ATTRIBUTE_META_DATA))
AND ATTRIBUTE_META_DATA IS NOT NULL
AND length(ATTRIBUTE_META_DATA) > 1
)
WHERE EMAIL_SOURCE IS NOT NULL
AND length(EMAIL_SOURCE) > 1;

3

Hello Sunny,

Thank you for your reply. While running to your query I did not get any record.
When I tried below query I get 200+ records:

SELECT  NAME FROM spt_identity 
	WHERE	
		xmltype(ATTRIBUTE_META_DATA).extract('/List/AttributeMetaData[@attribute="email"]/@modified').getStringVal() IS NOT NULL 
		AND ATTRIBUTE_META_DATA IS NOT NULL 
		AND length(ATTRIBUTE_META_DATA) > 1;

Could you please help me update this query so that I can get other two informaton which is there in option 4 like “user” and LastValue/String as HR_Email?

Thanks

4

Try this


SELECT NAME, USER_ID, LAST_VALUE FROM (
    SELECT  
        NAME NAME, 
        xmltype(ATTRIBUTE_META_DATA).extract('/List/AttributeMetaData[@attribute="email"]/@user').getStringVal() AS USER_ID,
        xmltype(ATTRIBUTE_META_DATA).extract('/List/AttributeMetaData[@attribute="email"]/LastValue/String/text()').getStringVal() AS LAST_VALUE
    FROM spt_identity 
    WHERE  
        XMLEXISTS('/List/AttributeMetaData[@attribute="email" and @user and /LastValue/String]' PASSING XMLType(ATTRIBUTE_META_DATA)) 
        AND ATTRIBUTE_META_DATA IS NOT NULL 
        AND length(ATTRIBUTE_META_DATA) > 1 
) 
WHERE USER_ID IS NOT NULL 
AND length(USER_ID) > 1 
AND LAST_VALUE IS NOT NULL 
AND length(LAST_VALUE) > 1;
2 Likes
5

@avikrsingh23
Please keep me posted if this worked or not.

6

Thanks Satish Its working now