Parse TSQL Stack

Parse the tsql_stack XML from an Extended Events session into a readable call stack. Paste the <frames> element from the XE event data into the @stackOrFrame variable.

The COALESCE handles both the old (handle/offsetStart/offsetEnd) and new (sqlhandle/stmtstart/stmtend) XE frame attribute names.

/* Paste the <frames></frames> here */
DECLARE @stackOrFrame xml = '' 

;WITH
xmlShred AS
(
    SELECT
        COALESCE
        (
            CONVERT(varbinary(64), f.n.value('.[1]/@handle', 'varchar(max)'), 1),
            CONVERT(varbinary(64), f.n.value('.[1]/@sqlhandle', 'varchar(max)'), 1)
        ) AS handle,
        COALESCE
        (
            f.n.value('.[1]/@offsetStart', 'int'),
            f.n.value('.[1]/@stmtstart', 'int')
        ) AS offsetStart,
        COALESCE
        (
            f.n.value('.[1]/@offsetEnd', 'int'),
            f.n.value('.[1]/@stmtend', 'int')
        ) AS offsetEnd,
        f.n.value('.[1]/@line', 'int') AS line,
        f.n.value('.[1]/@level', 'tinyint') AS stackLevel
    FROM @stackOrFrame.nodes('//frame') AS f(n)
)
SELECT
    xs.stackLevel,
    ca.outerText,
    ca2.statementText
FROM
    xmlShred AS xs
    CROSS APPLY sys.dm_exec_sql_text(xs.handle) AS dest
    CROSS APPLY (SELECT LTRIM(RTRIM(dest.text))  FOR XML PATH(''), TYPE) AS ca(outerText)
    CROSS APPLY
    (
        SELECT
            SUBSTRING
            (
                dest.text,
                (xs.offsetStart / 2) + 1,
                ((
                    CASE
                        WHEN xs.offsetEnd = -1
                            THEN DATALENGTH(dest.text)
                        ELSE xs.offsetEnd
                    END
                    - xs.offsetStart
                ) / 2) + 1
            )
        FOR XML PATH(''), TYPE
    ) AS ca2(statementText)
ORDER BY xs.stackLevel
OPTION (RECOMPILE);

I have CAST the text to XML so it's formatted nicely, but if your code contains XML-specific special characters, it might break.