Why is XML taking up more storage than VARCHAR(MAX)?

Question

We have large tables storing XML data as varchar(MAX). The data is for reference/historical purposes, it's not queried. Based on what I've read, storing as XML datatype instead of VARCHAR(MAX) should result in space savings, but my tests show otherwise. See below, where the size of t1_XML is smaller than t1_NVARCHARMAX, but larger than t1_VARCHARMAX.

set nocount on;

drop table t1_XML;
drop table t1_VARCHARMAX;
drop table t1_NVARCHARMAX;

create table t1_XML(col1 int identity primary key, col2 XML);
create table t1_VARCHARMAX(col1 int identity primary key, col2 varchar(max));
create table t1_NVARCHARMAX(col1 int identity primary key, col2 nvarchar(max));

go

declare @xml XML = '<root><element1>test</element1><element2>test</element2><element3>test</element3><element4>test</element4><element5>test</element5></root>'
    , @x int = 1;

while @x <= 10000
begin
    begin tran

    insert into dbo.t1_XML (col2) values (@xml);
    insert into dbo.t1_VARCHARMAX (col2) values (cast(@xml as varchar(max)));
    insert into dbo.t1_NVARCHARMAX (col2) values (cast(@xml as varchar(max)));

    commit tran

    set @x += 1;
end

exec sp_spaceused 'dbo.t1_XML';
exec sp_spaceused 'dbo.t1_VARCHARMAX';
exec sp_spaceused 'dbo.t1_NVARCHARMAX';

Answer 1

There are two things to know about the XML datatype that together explain what you are experiencing:

1. As noted in @EvanCarroll's answer, the XML datatype is optimized. Meaning, rather than repeat element and attribute names (which are typically repeated quite a bit and are a large part of why so many people, sometimes rightfully-so, complain about XML documents being so bulky), a dictionary / lookup list is created to store each unique name once, given a numeric ID, and that ID is used to populate the structure of the document. This is why the XML datatype is quite often a better way to store XML documents.
2. Additionally, the XML datatype uses UTF-16 (Little Endian) to store string values (both element and attribute names as well as any actual string content). This datatype does not use compression, so strings are essentially 2 or 4 bytes per character, with most characters being the 2-byte variety.

Looking at the particular test XML document you are using, and the VARCHAR datatype (1 to 2 bytes per character, most often the 1-byte variety), we can now explain what you are seeing as being a result of:

1. Each of your elements (root, element1, etc) are used only once, so the only savings of placing the names into the lookup list is to cut the size in exactly half. But, the XML type uses UTF-16 so the size of each string is twice as much, cancelling out the savings of moving the element names into the lookup list. At this point, if only looking at the document structure (i.e. element names) then there is should effectively be no difference between the XML type and the VARCHAR version.
2. But, the string content in each element (i.e. test) takes up twice the number of bytes: 8 bytes in XML as opposed to 4 bytes in VARCHAR. Given that there are 5 instances of "test" per each row, that is 20 extra bytes per row for the XML type. At 10k rows, that is 200,000 extra bytes of the 600,000 byte difference. The rest is internal overhead of the XML type and the additional page overhead of the additional number of datapages needed to store the same number of rows due to each row being slightly larger.

To better illustrate this behavior, consider the following two variations of XML data: the first being the exact same XML as in the question, and the second being almost the same, but with all elements being the same name. In the second version, all element names are "element1" so that they are the same length as each element in the original version. This is results in the VARCHAR data length being the same in both cases. But the element names being the same in the second version allow the internal optimizations to be more noticeable.

-- Original XML (unique element names -- "element1", "element2", ... "elementN"):
DECLARE @xml XML =  '<root><element1>test</element1><element2>test</element2>
<element3>test</element3><element4>test</element4><element5>test</element5></root>';
SELECT DATALENGTH(@xml) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml)) AS [VarcharBytes];

-- More "typical" XML (repeated element names -- all "element1"):
DECLARE @xml2 XML = '<root><element1>test</element1><element1>test</element1>
<element1>test</element1><element1>test</element1><element1>test</element1></root>';
SELECT DATALENGTH(@xml2) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml2)) AS [VarcharBytes];

Results:

ElementNames    XmlBytes    VarcharBytes
------------    --------    ------------
Unique          197         138
Non-Unique      109         138

Answer 2

From the docs on XML Data Type and Columns (SQL Server)

The data is stored in an internal representation that preserves the XML content of the data. This internal representation includes information about the containment hierarchy, document order, and element and attribute values. Specifically, the InfoSet content of the XML data is preserved. For more information about InfoSet, visit http://www.w3.org/TR/xml-infoset. The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.

binary_representation_size is roughly data + information about the containment hierarchy, document order, and element and attribute values - insignificant white spaces, order of attributes, namespace prefixes, and XML declaration

That's not a clear win if you have no namespace prefixes, and whitespace you're just storing more data.

It's also mentioned explicitly in the docs that you may just want to use nvarchar(max) if you're only storing and don't care about the features or validation,

If none of these conditions [need for advanced functionality] is met, you should use the relational data model. For example, if your data is in XML format but your application just uses the database to store and retrieve the data, an [n]varchar(max) column is all you require. Storing the data in an XML column has additional benefits. This includes having the engine determine that the data is well formed or valid, and also includes support for fine-grained query and updates into the XML data.

Answer 3

SQL Server 2016 introduced the COMPRESS function. Applying this to @Solomon's example:

... DATALENGTH(COMPRESS(CONVERT(VARCHAR(MAX), @xml))) AS [VarcharCompressed];

... DATALENGTH(COMPRESS(CONVERT(VARCHAR(MAX), @xml2))) AS [VarcharCompressed];

Further spaces saving is obtained:

ElementNames    XmlBytes    VarcharBytes  VarcharCompressed
------------    --------    ------------  -----------------
Unique          197         138           72
Non-Unique      109         138           49

It is note-worthy that space is saved for both unique and repeated element names.