SQL query to parse attribute_meta_data not working

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.

Hello All,

I am trying to create a xml parsing query usng spt_identity table.

When I execute sql query “Select attributes from spt_identity where name=‘211678016’;”
I get below XML data:

<Attributes>
  <Map>
    <entry key="CostCenter" value="S03100609"/>
    <entry key="EmpPopulation" value="Contractor"/>    
    <entry key="PersonTypeID" value="99999"/>
    <entry key="SSOID" value="211678016"/>
    <entry key="authSource" value="Universal"/>
    <entry key="businessUnitId" value="604200008"/>
    <entry key="department" value="Cyber Security I&amp;AM 2"/>
    <entry key="displayName" value="Singh, Avinash (211678016)"/>   
    <entry key="username" value="Singh, Avinash Kumar"/>    
    <entry key="workCountry" value="IN"/>
	...
  </Map>
</Attributes>

But when I try to make a query to get CostCenter value from the XML, I get errors.
Here is the query I tried:

SELECT
(xmltype(iden.attributes).extract(‘Attributes/Map/entry[@key="CostCenter"]/@value’).getStringVal()) AS CostCenter
FROM spt_identity iden WHERE iden.name=‘211678016’;

Here is the error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: ‘Attributes/Map/entry[@key="CostCenter"]/@value’
31011. 00000 - “XML parsing failed”
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.

Please help me to create the right queryPreformatted text``Preformatted text

2

Hi @avikrsingh23,

Could you check if the characters used are standard ASCII characters, especially the quotes (',") used.

Try if this works

SELECT
  xmltype(iden.attributes).extract('/Attributes/Map/entry[@key="CostCenter"]/@value').getStringVal() AS CostCenter
FROM 
  spt_identity iden 
WHERE 
  iden.name='211678016';
3

Thank you Sreeram for your quick reply, It worked for me.

4

Hi @avikrsingh23,

Glad that worked, could you please mark my reply as the solution?,

Best,
Sreeram

5

Now I need to find all identities where the identites do not have specific metadata in it.

There are three types of employees.
Some may have metadata like below:
<AttributeMetaData attribute="email" source="MailTFAYD:mail"/>
Some may have metadata like below:
<AttributeMetaData attribute="email" source="Application_HR:C001_EMAIL"/>
Others may have metadata like below:

<AttributeMetaData attribute="email" modified="1573068729516" user="20003200">
      <LastValue>
        <String>[email protected]</String>
      </LastValue>
    </AttributeMetaData>

Rest may not have this metadata attribute at all.

Now I need to find all those employees who do not have metada <AttributeMetaData attribute="email" source="MailTFAYD:mail"/>

I tried query like below:

SELECT iden.name
FROM
spt_identity iden
WHERE
xmltype(iden.ATTRIBUTE_META_DATA).extract(‘/List/AttributeMetaData[@attribute=“email”]/@source’).getStringVal() != null AND
xmltype(iden.ATTRIBUTE_META_DATA).extract(‘/List/AttributeMetaData[@attribute=“email”]/@source’).getStringVal() !=‘MailTFAYD:mail’;

Getting below error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
ORA-06512: at “SYS.XMLTYPE”, line 272
ORA-06512: at line 1
31011. 00000 - “XML parsing failed”
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.

6

Hi @avikrsingh23,

The error is due to non-standard ASCII characters in the query. Please try the following query.

If my response resolves your issue, please mark my response as the solution.

SELECT iden.name
FROM
spt_identity iden
WHERE
xmltype(iden.ATTRIBUTE_META_DATA).extract('/List/AttributeMetaData[@attribute="email"]/@source').getStringVal() IS NOT NULL AND
xmltype(iden.ATTRIBUTE_META_DATA).extract('/List/AttributeMetaData[@attribute="email"]/@source').getStringVal() != 'MailTFAYD:mail';
7

Getting same error:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
ORA-06512: at “SYS.XMLTYPE”, line 272
ORA-06512: at line 1
31011. 00000 - “XML parsing failed”
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.

8

Try this instead

SELECT iden.name
FROM spt_identity iden
WHERE NOT XMLExists(
    '/List/AttributeMetaData[@attribute="email" and @source="MailTFAYD:mail"]'
    PASSING XMLType(iden.ATTRIBUTE_META_DATA)
);

Let me know if this helps

9

Same error is coming while executing this updated query

10

When I try to get single record like using below query:
SELECT ATTRIBUTE_META_DATA FROM spt_identity where name=‘202678012’;
I am able to see the record like below:

<List>
  <AttributeMetaData attribute="businessUnitId" source="Application_HR:C001_BUSINESS_UNIT_ID"/>
  <AttributeMetaData attribute="CostCenter" source="Application_HR:C001_COST_CENTER"/>
  <AttributeMetaData attribute="LocationID" source="Application_HR:C001_LOCID"/>
  <AttributeMetaData attribute="jobTitle" source="Application_HR:C001_JOB_TITLE"/>
  <AttributeMetaData attribute="email" source="Application_HR:C001_EMAIL"/> 
  ....
</List>

And when I try to find that email metadata attribute using below query:
SELECT xmltype(iden.ATTRIBUTE_META_DATA).extract(‘/List/AttributeMetaData[@attribute=“email”]/@source’).getStringVal() as EM
FROM spt_identity iden
WHERE name=‘202678012’;

Then also I am able to get the answer as below:
Application_HR:C001_EMAIL

But when I try above query without where clause the getting the same error again.
Query:
SELECT xmltype(iden.ATTRIBUTE_META_DATA).extract(‘/List/AttributeMetaData[@attribute=“email”]/@source’).getStringVal() as EM
FROM spt_identity iden;
Result:
Same error.

Looks like when I try to parse xml for multiple records then the parsing is failing. Not sure how to resolve.

11

Thank you so much Sreeram for helping me out.
Looks like you gave correct query. The parsing error was due to junk data in that column.
When I added null checks in the query it worked fine. Here is the updated query I used:

SELECT NAME, EMAIL FROM (
SELECT
NAME, xmltype(ATTRIBUTE_META_DATA).extract(‘/List/AttributeMetaData[@attribute=“email”]/@source’).getStringVal() AS EMAIL
FROM spt_identity
WHERE
NOT 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 IS NOT NULL
AND length(EMAIL) > 1;
This query is returning output like below:

503157026 Application_HR:C001_EMAIL
604554092 Application_HR:C001_EMAIL
606585822 Application_HR:C001_EMAIL
606573427 Application_HR:C001_EMAIL

Thank you once again.

12

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