NetIQ IDM - JDBC driver - SQL calls from driver (Publisher channel) using XSLT

Recently I was working on a task where we had to call some SQL statements from publisher channel on a JDBC driver to different tables than the ones driver was configured to sync.

The official documentation suggest to achieve this using jdbc-statement but it only schedules them on the subscriber channel, best suited for calling some SQL for stored procedure.

The way I solved it was using XSLT and enabling Subscriber channel.

On the publisher  Command Transformation Channel , I have following XSLT:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:jdbc="urn:dirxml:jdbc" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor" version="1.0"> <xsl:param name="srcQueryProcessor" /> <xsl:param name="destQueryProcessor" /> <xsl:template match="node()|@*"> <xsl:copy> <xsl:apply-templates select="node()|@*" /> </xsl:copy> </xsl:template> <!-- ON ADD | MODIFY --> <xsl:template match="modify[@class-name='Group']|add[@class-name='Group']"> <!-- GET ATTRIBUTE VALUE TO USE WITH SQL --> <xsl:variable name="systmeid" select="*[@attr-name='businessCategory']//value" /> <xsl:variable name="query-doc"> <jdbc:statement type="query"> <jdbc:sql> <xsl:text>SELECT DISTINCT OPERATIONAL_OWNER_ID FROM idm_application_list WHERE SYSTEM_ID =</xsl:text> <xsl:value-of select="$systmeid" /> </jdbc:sql> </jdbc:statement> </xsl:variable> <!-- GET SQL RESULT SET --> <xsl:variable name="result" select="query:query($srcQueryProcessor,$query-doc)" /> <!-- SAVE SQL RESULT INTO TEMP ATTRIBUTE --> <xsl:copy> <xsl:apply-templates select="node()|@*" /> <add-attr attr-name="OperationOwnerId"> <xsl:for-each select="$result//jdbc:value"> <xsl:variable name="row" select="." /> <value type="string"> <xsl:value-of select="$row" /> </value> </xsl:for-each> </add-attr> </xsl:copy> </xsl:template> </xsl:stylesheet>

Actually  I am calling a SQL statement and result of the statement is saved into a temp attribute called  OperationOwnerId,  now this temp attribute is accessible to dirxml policy, remember when you have worked out finished with OperationalOwnerId attribute, just remove it from the current operation.   I have a policy with following rule on Publisher Command Transformation next to XSLT


<rule> <description>Clean up temporary attributes out of the operation for OperationOwnerId</description> <conditions> <and> <if-class-name mode="nocase" op="equal">Group</if-class-name> <if-op-attr name="OperationOwnerId" op="available" /> </and> </conditions> <actions> <do-strip-op-attr name="OperationOwnerId" /> </actions> </rule>