What is wrong with my XML query CTE?

Based on this answer, which has a query that should return a "call stack format" result set from the events in an Extended Events trace file.

A quick query

SELECT CONVERT (XML, event_data) AS data
        FROM sys.fn_xe_file_target_read_file ('C:\Temp\test_trace*.xel', 'C:\Temp\test_trace*.xem', NULL, NULL)

returns plenty of results, showing that the event is being logged, the final call stack query returns no results. That query is based on the following 2 CTEs:

WITH 
    CapturedResults AS
    ( SELECT data.value ( '(/event/@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],
        CAST(data.value('(/event/action[@name="tsql_stack"]/value)[1]','varchar(MAX)') AS XML)         AS [stack_xml]       
    FROM (
        SELECT CONVERT (XML, event_data) AS data
        FROM sys.fn_xe_file_target_read_file ('C:\BradySqlTrace\test_trace*.xel', 'C:\BradySqlTrace\test_trace*.xem', NULL, NULL)) entries
    ),

    StackData AS
    ( SELECT frame_xml.value('(./@level)', 'int')      AS [frame_level],
        frame_xml.value('(./@handle)', 'varchar(MAX)') AS [sql_handle],
        frame_xml.value('(./@offsetStart)', 'int')     AS [offset_start],
        frame_xml.value('(./@offsetEnd)', 'int')       AS [offset_end]
    FROM CapturedResults CROSS APPLY stack_xml.nodes('//frame') N (frame_xml)
    )
SELECT * FROM StackData

When I select from CapturedResults I get the expected no. of rows, but when I query StackData, I get zero results.

The XML node in question, which should produce the stack_xml column, looks as follows:

<event name="sp_statement_completed" package="sqlserver" timestamp="2017-05-08T12:37:15.132Z">
  ....
  <action name="tsql_stack" package="sqlserver">
    <value>
      <frames>
        <frame level="1" handle="0x03003D0084CC28352EF7C1006DA7000000000000000000000000000000000000000000000000000000000000" line="10" offsetStart="384" offsetEnd="466" />
        <frame level="2" handle="0x03003D00DAA5E13A62E99F006DA7000001000000000000000000000000000000000000000000000000000000" line="3" offsetStart="114" offsetEnd="-1" />
        <frame level="3" handle="0x01003D005CB2C92D10024E8F0000000000000000000000000000000000000000000000000000000000000000" line="2" offsetStart="4" offsetEnd="-1" />
      </frames>
    </value>
  </action>
</event>

I haven't posted the whole query, as I thought the second CTE producing no results was the start of the problem and I should first address that. The whole query also returns zero results.

You want to use .query() not .value() for xml->xml transformation. .value() is for xml->scalar

WITH Events as
    (
      SELECT CONVERT (XML, event_data) AS data
      FROM sys.fn_xe_file_target_read_file ('C:\temp\test*.xel', 'C:\temp\test*.xem', NULL, NULL) 
    ),
    CapturedResults AS
    ( SELECT data.value ( '(/event/@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.query('/event/action[@name=''tsql_stack'']/value' )                                       AS [stack_xml]       
    FROM Events
    ),
    StackData AS
    ( SELECT frame_xml.value('(./@level)', 'int')      AS [frame_level],
        frame_xml.value('(./@handle)', 'varchar(MAX)') AS [sql_handle],
        frame_xml.value('(./@offsetStart)', 'int')     AS [offset_start],
        frame_xml.value('(./@offsetEnd)', 'int')       AS [offset_end]
    FROM CapturedResults CROSS APPLY stack_xml.nodes('//frame') N (frame_xml)
    )
SELECT * 
FROM StackData

David