8

I try to understand how SQL Server 2008 store xml columns in an attempt to estimate table size in our product.

I'm using DATALENGTH(xml_column) to run some test and results are disturbing :

Xml document length | Datalength | Bytes per character
175                 | 366        | 2.09
15                  | 38         | 2.53
314                 | 414        | 1.31

Obviously, the xml column type is not a nvarchar(max) in disguise, as I've read somewhere.

It's probably compacted and stored as binary, but a can't find how anywhere.

Can someone explain me how is an xml column stored in SQL Server 2008 ?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Nicolas Repiquet
  • 9,097
  • 2
  • 31
  • 53

2 Answers2

7

An XML column is stored in a "compact binary format".

I doubt that Microsoft will give you any more detail than that.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
  • 4
    And it's important to know it's a "tokenized" format, e.g. Microsoft guarantees to give back a **semantically** identical XML - but they do not guarantee the textual representation of the XML (e.g. MS will switch `` to `` upon returning from their XML column, and as far as I know, there's no way to control that behavior. Don't rely on getting back a textually identical XML from SQL Server .... – marc_s Nov 30 '12 at 18:17
  • 1
    Isn't compact binary format the same as [ms-binxml](http://msdn.microsoft.com/en-us/library/ee208875(v=sql.105).aspx) – rene Nov 30 '12 at 20:05
  • I have no idea if it's the same or not - and no interest, either. For all I care, it can change from one day to the next day, as long as it doesn't break anything. – John Saunders Nov 30 '12 at 22:51
  • @rene, this is a great document and it answers my question. – Nicolas Repiquet Dec 01 '12 at 15:50
5

As pointed out by rene, SQL Server 2008 uses the MS-BINXML - a compact binary format - to store the content of xml columns.

Community
  • 1
  • 1
Nicolas Repiquet
  • 9,097
  • 2
  • 31
  • 53