XML Type directive

XmlTypeDirective.sql

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