Good or bad, I went for the following:
set @v_xml_nest_level_pos = CHARINDEX ('<tsql_stack><frame nest_level', @p_additional_info, 0);
-- nest_level found
if @v_xml_nest_level_pos > 0
begin
-- Replace single quotes with double quotes to make it valid XML
set @xmlString = REPLACE(@p_additional_info, '''', '"');
-- Now cast to XML
set @xml = TRY_CAST(@xmlString AS XML);
-- Extract nest_level as string
set @nest_level_str = @xml.value('(/tsql_stack/frame/@nest_level)[1]', 'NVARCHAR(100)');
-- Try casting to INT safely
SET @nest_level = TRY_CAST(@nest_level_str AS INT);
if @nest_level is null
begin
set @P_SEO_CURR_USER = 1;
end;
if @nest_level is not null
begin
if @nest_level < 1
begin
set @P_SEO_CURR_USER = 1;
end;
end;
end
else -- nest_level not found
begin
set @P_SEO_CURR_USER = 1;
end;
where:
p_additional_info - is field additional_information of fn_get_audit_file, within a loop of which the above code is run.
@P_SEO_CURR_USER is set as default 0, meaning non-top-level SQL. Set to 1 when top-level.
Thank you to all commenters who helped me on this.
best regards
Altin