Что не так с моим XML-запросом CTE?

Question

На основе этого ответа, который имеет запрос, который должен возвращать набор результатов "формат стека вызовов" из событий в файле трассировки расширенных событий.

Быстрый запрос

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)

возвращает множество результатов, показывающих, что событие регистрируется, окончательный запрос стека вызовов не возвращает результатов. Этот запрос основан на следующих 2 CTE:

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

Когда я выбираю из CapturedResults Я получил ожидаемое нет. строк, но когда я запрашиваю StackDataЯ получаю ноль результатов.

Рассматриваемый узел XML, который должен производить stack_xml столбец, выглядит следующим образом:

<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>

Я не опубликовал весь запрос, так как думал, что второй CTE, не дающий результатов, стал началом проблемы, и я должен сначала решить эту проблему. Весь запрос также возвращает ноль результатов.

Answer 1

Вы хотите использовать .query(), а не.value() для преобразования xml->xml. .value() для 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

Дэвид