SQLXML: How to get related role ad report from a Blackbaud Task (TASKPECXML)

When working with Blackbaud Enterprise CRM, you may create tasks that point to a report. Here’s how to extract the related report and roles from a task:

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi,
                     'http://www.w3.org/2001/XMLSchema' AS xsd,
             'bb_appfx_commontypes' AS common,
             'bb_appfx_pagedefinition' AS pagedef,
              DEFAULT 'bb_appfx_task'), 
taskcte AS 
(
SELECT  
   TC.NAME TASKNAME,
   SR.NAME ROLENAME, 
   R.SYSTEMROLEID, 
   TC.TASKSPECXML,
   TC.TASKSPECXML.value('(//TaskSpec/common:ShowReport/@ReportID)[1]', 'varchar(100)') REPORTID_FROMTASKSPEC,
   TC.TASKSPECXML.value('(//TaskSpec/common:ShowPage/@PageID)[1]', 'varchar(100)') PAGEID 
FROM
    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_TASK R
    INNER JOIN SYSTEMROLE SR
    ON R.SYSTEMROLEID = SR.ID
    INNER JOIN TASKCATALOG TC
    ON R.TASKID = TC.ID
)

SELECT * 
FROM taskcte