Which datatype to store XML data in: VARCHAR(MAX) or XML

Question

I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time to time; although not frequently; I'll need to query the XML to find element and attribute values. If left to my own devices, I would tend to use the XML data type although I’ve been led to believe this is wrought with issues. So that leads me to my questions.

Given this scenario, what factors should I be considering when trying to decide between storing XML in an XML column vs. a varchar(MAX) column

If it helps… here are some additional details:

• No decision has been made regarding the use of schema’s for these fragments (e.g. XSD’s)
• Sizes of the fragments will range from small to very large
• All XML will be well-formed
• Over the course of a day, there will be up to ~10,000 fragments collected with online query support needed for ~3 months
• Queries against the XML will happen throughout the day but should remain light with few concurrent queries of this type

Answer 1

If Queries against the XML will happen by sql server xml capabilities, then use XML type to store a xml to avoid casting

And

keep in mind, that XML type may be stored little bit slower due to xml validation, but underlying type of XML is ordinary varbinary(max)

Answer 2

what factors should I be considering when trying to decide between storing XML in an xml column vs. a varchar(MAX) column

The factors are:

1. The XML type is queryable / parseable through XQuery expressions, including being able to use FLWOR Statement and Iteration

2. Data in XML variables and columns can be modified inline using XQuery expressions via XML DML.

3. XML data is stored as UTF-16 LE (Little Endian), so VARCHAR(MAX) would be a poor choice as it could result in data loss. Hence, the true decision should be between XML and NVARCHAR(MAX), given that NCHAR / NVARCHAR is also UTF-16 LE.

4. XML data can be validated against an XSD / XML SCHEMA COLLECTION. No validation (outside of ensuring well-formedness) is done if no XML Schema Collection is specified, but this option is not available when using NVARCHAR(MAX).

5. One major benefit of the XML type is that it is stored in a highly optimized format (not VARBINARY(MAX) as stated in @Oleg's answer) that does not store the exact string representation that you see, but instead has a dictionary of Element and Attribute names and refers to them by their ID. It also removes whitespace. Try the following:

   DECLARE @Test1 XML = N'<Test><TagName>1</TagName><TagName>2</TagName></Test>';
   
   DECLARE @String1 NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @Test1);
   
   SELECT DATALENGTH(@Test1) AS [XmlBytes],
          LEN(@String1) AS [StringCharacters],
          DATALENGTH(@String1) AS [StringBytes];
   
   SET @Test1 = N'<Test><TagName>1</TagName><TagName>2</TagName><TagName>3</TagName>
   <TagName>4</TagName><TagName>5</TagName><TagName>6</TagName></Test>';
   
   SET @String1 = CONVERT(NVARCHAR(MAX), @Test1);
   
   SELECT DATALENGTH(@Test1) AS [XmlBytes],
          LEN(@String1) AS [StringCharacters],
          DATALENGTH(@String1) AS [StringBytes];
   

   Returns:

   XmlBytes   StringCharacters   StringBytes
   56         53                 106
   
   XmlBytes   StringCharacters   StringBytes
   84         133                266
   

   As you can see in the example output above, adding four elements (#s 3, 4, 5, and 6) added 80 characters (hence 80 bytes if using VARCHAR) and 160 bytes to the NVARCHAR variable. Yet, it only added 28 bytes to the XML variable, which is less than it added for VARCHAR (just in case someone was going to argue in favor of VARCHAR over XML because XML is UTF-16 which is [mostly] double-byte). This optimization can save tons of space, and is reason enough by itself to use the XML datatype.

6. XML data can be indexed via specialized XML indexes

Answer 3

A major contributor to the difference in storage between NVARCHAR(MAX) not discussed in the original answer is that NVARCHAR(MAX) does not use SCSU; using a NVARCHAR(N) + NVARCHAR(MAX) column pairs can reduce the storage requirements significantly, in addition to the Page Compression mentioned below. See Manual SCSU 'compression' in SQL Server 2017 for NVARCHAR(MAX)

—

One consideration that is not present in the other answers relates to Page Compression. With Page Compression, there are some very specific use cases, where storing XML as NVARCHAR(MAX) may be beneficial, depending on some specific factors.

This is a special scenario and should only be considered after data and usage analysis. In the scenario presented, this appears unlikely.

Considerations of when one might consider NVARCHAR(MAX) over XML:

• Page Compression is used. The third level is Dictionary Compression.
• The XML fragments are small (eg. < 1/4 page characters) and contain sufficient repetitive values and/or have common prefixes that page compression can eliminate.
• Disks costs $$$ and a primary business-driven goal is to reduce disk usage at the cost of other features. (Don't bother if disk space is ample!)
• The fields are saved in In-Row Data, as LOB data does not benefit from Page Compression.
• Page compression actually allows more rows to fit on a single page. (Don't bother if additional rows cannot be combined onto a single page!)
• XML queries against the fields are infrequent, as to avoid the cost of parsing. Such an approach is more effective for 'cold storage'.

Some drawbacks and counter-arguments:

• No native validation of XML in field, typed or otherwise.
• XML indices cannot be used.
• Pay CPU cost on every conversion of the text back to an XML type.
• Page Compression efficiency over XML encoding varies based on the amount of duplication in values (including text nodes) on records within a single page.
• Page Compression will not benefit large XML fragments moved to LOB. XML encoding is expected to result in smaller disk usage here. One might split fields and use COMPRESS for large XML data, although such is adding yet another level of complexity with additional concerns.

In a very specific scenario I've been working on, using NVARCHAR(MAX) results in 20%+ disk usage reduction when applied to many small fragments with high duplication. YMMV. Test with actual data and usages.