Menu
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 ?
600
In SQL Server, you usually store XML data in a column configured with the xml data type. The data type supports several methods that let you query and modify individual elements, attributes, and their values directly within the XML instance, rather than having to work with that instance as a whole.
XML documents you insert into columns of type XML can reside either in the default storage object, or directly in the base table row. Base table row storage is under your control and is available only for small documents; larger documents are always stored in the default storage object.
Create columns and variables CREATE TABLE T1(Col1 int primary key, Col2 xml); You can use a DECLARE statement to create a variable of xml type, as the following example shows. DECLARE @x xml; Create a typed xml variable by specifying an XML schema collection, as shown in the following example.
In the first approach, we can use database management system in which we store the document as a text and the relational and object DBMS are used to store XML whole document as a text field in the DBMS record and object.
An XML column is stored in a "compact binary format".
I doubt that Microsoft will give you any more detail than that.
65
As pointed out by rene, SQL Server 2008 uses the MS-BINXML - a compact binary format - to store the content of xml columns.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
