Parse JSON data in HRC_ATOMPUB_ENTRIES.ENTRY_XML field

Summary:

For reporting purposes we need to read and parse the JSON payload that is present in Atom Feed Table. The field ENTRY_XML contains an XML which tag //entry/content contains a JSON formatted information regarding the Atom Feed event. The JSON string is not possible to parse it and the SQL query launched in BIP Report is not working.

Content (required):

The query, as an example, tries to extract the JSON payload and it works, but if we use the JSON_VALUE function the parsing is not possible, for example in HCM a phone change creates a JSON string like: