Explore our sites

Stack Exchange
tour help
Take the tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
2  
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????? –  marc_s Sep 27 at 8:59
add comment

2 Answers

up vote 2 down vote

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. :-).

share|improve this answer
 
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. –  a1ex07 Sep 28 at 2:59
add comment
up vote 1 down vote

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.