SQL/SMP: List Automation Policies Email Addresses (xml)

AeX_Show_Automation_Policy_Email_Recipients.sql

-- List Automation Policies Email Addresses
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
	[Automation Policy] = [conn1].[Name]
	, [TO_EMAIL] = CAST([conn3].[State] AS XML).value('(/item/UniversalTaskManagementDataSourceAdapter/ActionParametersMap/SerializableDictionary/entry/value/ActionParameterMappingTuple[ActionParamName="TO_EMAIL"]/CustomValue/string)[1]', 'varchar(max)')
	, [CC_EMAIL] = CAST([conn3].[State] AS XML).value('(/item/UniversalTaskManagementDataSourceAdapter/ActionParametersMap/SerializableDictionary/entry/value/ActionParameterMappingTuple[ActionParamName="CC_EMAIL"]/CustomValue/string)[1]', 'varchar(max)')
FROM
	[vItem] [conn1]
	-- Connect to Altiris.Automation.AutomationPolicies.ActionDataSourceAdapters.UniversalTaskManagementDataSourceAdapter
	LEFT JOIN [vItemReference] [conn2] ON [conn1].[Guid] = [conn2].[ParentItemGuid] AND [conn2].[Hint] = 'automationdatasourceadapter'
	LEFT JOIN [vItem] [conn3] ON [conn3].[Guid] = [conn2].[ChildItemGuid]
WHERE
-- Altiris.Automation.AutomationPolicies.AutomationPolicy
[conn1].[ClassGuid] = 'BEC44BD0-F31C-43C2-9391-52F176D67A04'
-- State is not NULL
AND	NOT [conn3].[State] IS NULL
-- To/Cc are not blank or NULL
AND NOT (
	ISNULL((CAST([conn3].[State] AS XML).value('(/item/UniversalTaskManagementDataSourceAdapter/ActionParametersMap/SerializableDictionary/entry/value/ActionParameterMappingTuple[ActionParamName="TO_EMAIL"]/CustomValue/string)[1]', 'varchar(max)')), '') = ''
	AND
	ISNULL((CAST([conn3].[State] AS XML).value('(/item/UniversalTaskManagementDataSourceAdapter/ActionParametersMap/SerializableDictionary/entry/value/ActionParameterMappingTuple[ActionParamName="CC_EMAIL"]/CustomValue/string)[1]', 'varchar(max)')), '') = ''
)
ORDER BY [conn1].[Name]