Xml stored as varbinary

Question

I'm wondering if I have to store a XML information inside a column (just storing, not for querying ) (SQL SERVER) which Datatype is better to use XML or Varbinary? I did an example:

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>Juan</FirstName>
<LastName>Perez</LastName>
</Person>
<Person id="98765">
<FirstName>Alfredo</FirstName>
<LastName>Domiguez</LastName>
</Person>
</People>'
Declare @ClientListBinary Varbinary(max)
set @ClientListBinary = CONVERT (varbinary(max),@clientlist,1)
print datalength(@ClientList)
print datalength(@ClientListBinary)

and the result is that Varbinary uses more space... any thoughts on that

• marc_s: The XML datatype is highly preferable - it stores the XML in a tokenized, optimized fashion and thus uses less storage than a comparable binary or string format. Also: using the XML datatype enables XML operations on the data. If your version of SQL Server has the XML datatype, and your data is XML - then why on earth would you not use the XML datatype?????

Answer 1

If your RDBMS has an xml datatype, why would you not use it?

For example, in SQL Server, you should put XML data in an xml column, because:

• The XML data can be compared to an XML Schema Collection, to give the functionality of typed XML.

• The XML data can be shredded with XQuery later.

• The XML data can be indexed with a number of different XML index types, which help the XQuery shredding.

As far as I can see, the only reason to use varbinary(max) over xml is if you expect to be putting more than 2GB of data into one field. (Of course, there may be other reasons, which I'm sure I'll learn from the posts here. :-).

• a1ex07: Just to add another reason when one may prefer varbinary over xml... The framework used by application might not work well with XML type (as far as I understand it's not supported by all drivers properly). Surely it's not an issue if all parts of the system are working on Microsoft platform.

Answer 2

Use the XML datatype. If wanting to use varbinary you'll need to convert to retrieve and store the data, so I'd advice against that.

You might as well use the proper datatype for data. Almost always - any possible resource or performance gain would be outweighed by difficulty of use and maintenance.

Plus if you're worried about performance/space usage on such a detailed level - chances are there are much better places to focus on to begin with where the gain would be much more substantial in comparison.