3

I am currently researching the best way to store an XML file in SQL Server 2000.

I don't necessarily need to map the XML fields and shred the XML file into a database. I'm really just looking for a way to hold the entire file in a SQL Blob field or something similar.

Basically I'm going from a typed dataset into an XML representation of that typed dataset.

I then want to take that XML file and store it in SQL Server for retrieval later.

Improve this question
7
  • Why not just put the XML file on the filesystem and store a pointer in the database? Your choices in SQL Server 2000 (why are you still using 2000???) are ntext or varbinary. Have you searched on this topic at all? Commented Feb 21, 2013 at 19:08
  • Any particular reason you want to store it in the database as opposed to in the file system? Commented Feb 21, 2013 at 19:08
  • Aaron & Rickard - We have many customers who are still using MSSQL2000, and we cannot change that because our customers are government entities.. and you can imagine how hard is to change something.. About the pointer: Particularly I think this is the best solution, but our system do not use any filesystem.. we just have the "EXE" file and the database... and, the worst part, our application was built in VB6... =( Commented Feb 21, 2013 at 19:18
  • 1
    Ugh, sorry to hear about all that. Commented Feb 21, 2013 at 19:24
  • Well you seem to have answered your own question then, just store it like you would any other blob. What language is doing the work? Commented Feb 21, 2013 at 19:30

1 Answer 1

Reset to default
3

I would use the IMAGE type, as opposed to any sort of TEXT. While I am not debating what Rickard Andersson said, XML is text, I can still remember an issue where an XML with French characters got its content ruined when read as text (as in XmlDocument.LoadXml), due to a combination of the XML processing instruction encoding declaration, the character set in the database (and maybe something else); basically the round trip didn't work (write/read).

With a binary storage, it is guaranteed to work, since your application completely controls the encoding/decoding of the binary stream. You can also employ compression much easily, a very much needed feature if your XML files are, or will ever grow, large.

The only downside has to do with the ability to "peek" more easily at the XML (i.e. tools will not attempt to show you text, but hex bytes). Otherwise, full-text indexes would work just fine...

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.