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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 devises, 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
share|improve this question
The xml type doesn't guarantee to preserve the exact form of the original xml, if there is a requirement for the document to unchanged then nvarchar(max) is the only option. – MartinC Jan 22 '12 at 20:20
@MartinC If the fragment is well-formed already what kind of change could occure? I believe you, I just haven't heard this before... Can you point me to some more details? – JoeGeeky Jan 22 '12 at 22:15
See "The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration." – Paul White Jan 23 '12 at 3:47
Example, empty tags <foo></foo> will become <foo /> – gbn Jan 23 '12 at 7:17
@gdn Ahhh, ok... this does not change the meaning, so that is ok with me. – JoeGeeky Jan 23 '12 at 12:51
up vote 4 down vote accepted

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


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)

share|improve this answer

Your Answer


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.