# Issue in query to read Attribute\_Meta\_Data

**URL:** <https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623>\
**Category:** IIQ Discussion and Questions\
**Tags:** identityiq, rules\
**Created:** [March 8, 2024, 7:28pm UTC](https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623 "2024-03-08T19:28:55Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![avikrsingh23](https://avatars.discourse-cdn.com/v4/letter/a/5fc32e/32.png) [@avikrsingh23](https://developer.sailpoint.com/discuss/u/avikrsingh23)\
**Post date:** [March 8, 2024, 7:28pm UTC](https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623/1 "2024-03-08T19:28:55Z")

</div>

### 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"/>`
4. 

```auto
<AttributeMetaData attribute="email" modified="1573068729516" user="206532010">
	<LastValue>
		<String>Eco.Feri11@gmail.com</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”:

```auto
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.

---

<div class="post-metadata">

**Author:** ![ajmerasunny1](https://avatars.discourse-cdn.com/v4/letter/a/3be4f8/32.png) [@ajmerasunny1](https://developer.sailpoint.com/discuss/u/ajmerasunny1)\
**Post date:** [March 8, 2024, 8:19pm UTC](https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623/2 "2024-03-08T20:19:03Z")

</div>

> [@avikrsingh23](#):
>
> ```auto
> <AttributeMetaData attribute="email" modified="1573068729516" user="206532010">
> <LastValue>
> <String>Eco.Feri11@gmail.com</String>
> </LastValue>
> </AttributeMetaData>
> 
> ```

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;

---

<div class="post-metadata">

**Author:** ![avikrsingh23](https://avatars.discourse-cdn.com/v4/letter/a/5fc32e/32.png) [@avikrsingh23](https://developer.sailpoint.com/discuss/u/avikrsingh23)\
**Post date:** [March 9, 2024, 8:46am UTC](https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623/3 "2024-03-09T08:46:39Z")

</div>

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:

```auto
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

---

<div class="post-metadata">

**Author:** ![iamksatish](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/iamksatish/32/26217_2.png) [@iamksatish](https://developer.sailpoint.com/discuss/u/iamksatish)\
**Post date:** [March 11, 2024, 1:22pm UTC](https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623/4 "2024-03-11T13:22:13Z")

</div>

Try this

```auto

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;
```

---

<div class="post-metadata">

**Author:** ![iamksatish](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/iamksatish/32/26217_2.png) [@iamksatish](https://developer.sailpoint.com/discuss/u/iamksatish)\
**Post date:** [March 12, 2024, 3:20am UTC](https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623/5 "2024-03-12T03:20:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![avikrsingh23](https://avatars.discourse-cdn.com/v4/letter/a/5fc32e/32.png) [@avikrsingh23](https://developer.sailpoint.com/discuss/u/avikrsingh23)\
**Post date:** [March 12, 2024, 3:47am UTC](https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623/6 "2024-03-12T03:47:05Z")

</div>

Thanks Satish Its working now

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/sailpoint/original/2X/f/f2136700ed5e3703e0b85e02f6be799dacca7735.png) [@system](https://developer.sailpoint.com/discuss/u/system)\
**Post date:** [May 11, 2024, 3:47am UTC](https://developer.sailpoint.com/discuss/t/issue-in-query-to-read-attribute-meta-data/34623/7 "2024-05-11T03:47:43Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.