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
Best Answer
If Queries against the XML will happen by sql server xml capabilities, then use XML type to store a xml to avoid casting
And
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)