XML Query keeps converting < to &lt; and > to &gt;

Question

bigxml4u

Some query plans are too large to be properly stored as XML. You get the error: XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

Which is fine. Everything needs limitations to prevent misuse. Who knows what would happen at 129 nested levels.

Of course, with query plans, you can save the XML from text format as a .sqlplan file, and then re-open it as a graphical execution plan.

The problem I'm having with my query is that when I try to display that, < is changed to < and > to >. This adds an unfortunate extra two steps before you can save the file as a valid .sqlplan, which can be reopened.

Right now, my query does this:

    query_plan = 
         CASE
             WHEN TRY_CAST(qsp.query_plan AS XML) IS NOT NULL
             THEN TRY_CAST(qsp.query_plan AS XML)
             WHEN TRY_CAST(qsp.query_plan AS XML) IS NULL
             THEN 
                 (
                     SELECT
                         (
                             SELECT
                                 N''-- '' + NCHAR(13) + NCHAR(10) +
                                 N''-- This is a huge query plan.'' + NCHAR(13) + NCHAR(10) +
                                 N''-- Remove the headers and footers, save it as a .sqlplan file, and re-open it.'' + NCHAR(13) + NCHAR(10) +
                                 N''-- Depending on local factors, you may need to replace "<" and ">" too.'' + NCHAR(13) + NCHAR(10) +
                                 N''-- '' + NCHAR(13) + NCHAR(10) +
                                 REPLACE(qsp.query_plan, N''<RelOp'', NCHAR(13) + NCHAR(10) + N''<RelOp'') +
                                 NCHAR(13) + NCHAR(10) + N''--'' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
                         )
                         FOR XML PATH(N''''), 
                                 TYPE
                 )
         END,

Normally, I'd follow my own advice and add this to the end, but doing so brings me back to the original XML error about nodes and nesting and 128, etc.

.value
     (
         './text()[1]', 
         'nvarchar(max)'
     );

I've tried adding REPLACE in the inner and outer selects to try to fix things, but the blob seems immune to my charms.

Is there a way to prevent or circumvent the conversion of these characters to make saving the file off a bit easier?

If you have a copy of the StackOverflow database locally, you can generate a query plan that ends up null in TRY_CAST, and throws and error when treated as XML with this query:

WITH
    p0 AS (SELECT pt.* FROM dbo.PostTypes AS pt),
    p1 AS (SELECT pp.* FROM p0 AS p CROSS JOIN p0 AS pp),
    p2 AS (SELECT pp.* FROM p1 AS p CROSS JOIN p1 AS pp),
    p3 AS (SELECT pp.* FROM p2 AS p CROSS JOIN p2 AS pp),
    p4 AS (SELECT pp.* FROM p3 AS p CROSS JOIN p3 AS pp),
    p5 AS (SELECT pp.* FROM p4 AS p CROSS JOIN p4 AS pp),
    p6 AS (SELECT pp.* FROM p5 AS p CROSS JOIN p5 AS pp),
    p7 AS (SELECT pp.* FROM p6 AS p CROSS JOIN p6 AS pp),
    p8 AS (SELECT pp.* FROM p7 AS p CROSS JOIN p7 AS pp),
    p9 AS (SELECT pp.* FROM p8 AS p CROSS JOIN p8 AS pp)
SELECT
    c = COUNT_BIG(*)
FROM p9 AS p
OPTION(RECOMPILE);

If you'd just like to see the generated nonsense, you can use this GitHub gist link.

Answer 1

You are doing one level of select too many in the problematic part of your code.

Your version simplified

select (
       select '<>' as [processing-instruction(query_plan)]
       )
for xml path(''), type;

Result

<>

Try this instead with for xml path directly on the processing instruction query

select '<>' as [processing-instruction(query_plan)]
for xml path(''), type;

Result

<?query_plan <>?>

Explanation:

select '<>' as [processing-instruction(query_plan)] on it's own does nothing but returning the string value with some strange looking column name.

It is when you add for xml path that magic happens and the processing instruction becomes part of the xml.

The query select (select '<>' as [processing-instruction(query_plan)]) will return the string value without the fancy column name and because of that for xml path does not create a processing instruction for you. Instead it is just a string that gets entitized as it should be for valid XML.