XQuery on Extended Events joining with dm_exec_query_plan not working I have this XQuery for my extended event session which works fine but when I try joining the plan_handle to dm_exec_query_plan to get the query plan, it says 


  Msg 257, Level 16, State 3, Line 1 Implicit conversion from data type
  varchar to varbinary is not allowed. Use the CONVERT function to run
  this query.


If I convert to varbinary, this is the error I receive.


  "Msg 569, Level 16, State 5, Line 1 The handle that was passed to
  dm_exec_query_plan was invalid. "


If I manually pass the handle into the DMF, it works just fine. This is how it looks like in varchar format (after adding the 0x)


  0x06000700f9971028f01033ae0300000001000000000000000000000000000000000000000000000000000000


SELECT
        q.Time
        ,q.[CPU (ms)]
        ,q.[Duration (s)]
        ,q.[SQL Statement]
        ,q.plan_handle
FROM (
SELECT
        data.value(     '(/event[@name="sql_statement_completed"]/@timestamp)[1]', 'DATETIME') AS [Time]
        ,data.value(        '(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)]
        ,CONVERT(float, data.value('(/event/data[@name="duration"]/value)[1]', 'BIGINT')) / 1000000
        AS [Duration (s)]
        ,data.value(
        '(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement]
        --,data.value('(/event/action[@name=''plan_handle'']/value)[1]', 'varchar(4000)')       AS [PlanHandle]
        ,'0x' + CAST(data.value('(/event/action[@name="plan_handle"]/value)[1]', 'varchar(4000)') AS varchar(4000)) AS plan_handle
    FROM (
        SELECT
            CONVERT(xml, event_data) AS data
        FROM sys.fn_xe_file_target_read_file
        ('H:\SQLskills\EE_ExpensiveQueries*.xel', 'H:\SQLskills\EE_ExpensiveQueries*.xem', NULL, NULL)
    ) entries
    )q
    CROSS APPLY sys.dm_exec_query_plan(plan_handle)
This is still a string, regardless of how it looks to you when you copy the output:

'0x' + CAST(...) AS varchar(4000)) AS plan_handle


Try using a style parameter on the CONVERT, so that the string isn't converted to a binary representation of the string, but rather converted to the string's literal binary value:

CONVERT(VARBINARY(32), '0x' + CAST(...) AS varchar(4000), 1) AS plan_handle