Generate XML documents efficiently

Foreword

"It depends" is the DBA's most frequent answer, and for a good reason. Most of the time giving good advice really depends on many variables. A good DBA must follow up by explaining "why" it depends.

Another often-repeated mantra is "There are no silver bullets." Everything is a trade-off. Parallelism might decrease query duration, for example, but it increases CPU usage.

Well, this blog post (and hopefully series) will try to cover a few of those very rare silver bullets. Now I'm cheating a bit in my definition, these tips will be along the lines "did you know this existed?" and the alternative is writing the query in a sub-optimal way. Let's get to it then!

TYPE directive in FOR XML

XML is a very polarizing topic in the SQL community, some developers might be lucky and never run into it. But love it or hate it, it is there and will be for a while so we might as well use it in the most efficient manner.

Let's say our goal is to generate an XML document from a query and pass it to an application or Service Broker, etc. We're going to use the FOR XML query. I'm testing this in a fresh SQL Server 2019 instance in the master database.

I'm going to generate a small XML document into an xml variable both with and without the TYPE directive. I'll repeat the experiment but save it into the varchar(MAX) and nvarchar(MAX) variables.

/* xml variable, no TYPE directive */
DECLARE @xmlNoType xml
SET @xmlNoType =
(
    SELECT *
    FROM sys.all_objects
    FOR XML PATH(''), ROOT ('Document')
)
SELECT (DATALENGTH(@xmlNoType) / 1024.0) / 1024. AS SizeMB
GO
/* xml variable, TYPE directive */
DECLARE @xmlType xml
SET @xmlType =
(
    SELECT *
    FROM sys.all_objects
    FOR XML PATH(''), ROOT ('Document'), TYPE
)
--SELECT (DATALENGTH(@xmlType) / 1024.0) / 1024. AS SizeMB
GO
/* varchar(MAX) variable, no TYPE directive */
DECLARE @varchar varchar(MAX)
SET @varchar =
(
    SELECT *
    FROM sys.all_objects
    FOR XML PATH(''), ROOT ('Document')
    /*
        Cannot use TYPE:
        Implicit conversion from data type xml to varchar(max)
        is not allowed. Use the CONVERT function to run this query.
    */
)
SELECT (DATALENGTH(@varchar) / 1024.0) / 1024. AS SizeMB
GO
/* nvarchar(MAX) variable, no TYPE directive */
DECLARE @nvarchar nvarchar(MAX)
SET @nvarchar =
(
    SELECT *
    FROM sys.all_objects
    FOR XML PATH(''), ROOT ('Document')
    /*
        Cannot use TYPE:
        Implicit conversion from data type xml to nvarchar(max)
        is not allowed. Use the CONVERT function to run this query.
    */
)
SELECT (DATALENGTH(@nvarchar) / 1024.0) / 1024. AS SizeMB
GO
/*
    XML size            0.59 MB
    varchar(max) size    0.90 MB
    nvarchar(max) size    1.80 MB
*/

Results on my machine are the following: first the size. Unsurprisingly both with and without TYPE directive, the XML size is the same - roughly 0.59 MB varchar(MAX) variable is 0.9 MB and nvarchar(MAX) is double that at 1.8 MB.

Let's look at the CPU and elapsed time.

Using TYPE saved about 6k reads and cut both CPU and duration to roughly half compared to the non-TYPE version. The non-xml variables aren't bad performance-wise either, but they come with more reads and a larger data type footprint.

Let's compare the actual execution plan:

In the query without the TYPE there is an implicit conversion. The TYPE directive tells SQL Server you want to generate an XML document. Without that, it generates an nvarchar(MAX) and then implicitly casts it to XML.

DECLARE @whatIsThis SQL_VARIANT
SET @whatIsThis =
(
    SELECT *
    FROM sys.all_objects
    FOR XML PATH(''), ROOT ('Document')
)

That returns an error message:

I've repeated the tests but doing a cartesian product with several values to enlarge the XML. These are the results for 6 MB and 21 MB XML respectively. The varchar(max) size was 20 MB and 70 MB (nvarchar is doubled).

With larger documents, the benefits only grow for such a small change. It earns its spot in the Silver bullets series. I've run into processes that generated XMLs over a hundred MBs and the savings were vast.

Thank you for reading