Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spock
Starting Member
35 Posts |
 Posted - 2002-09-24 : 23:43:38
|
| Hi,I have a requirement of storing a XML document in the database. Whenever we retrieve the XML we need the whole XML document each time and not just a part of it. Is it better to use CLOB or go in for the new XML features suported in SQL Server 2000. I also have a choice of deciding between SQL Server 2000 and 7.0. We plan on using Java as our middleware.Please advise me on the best option. If the requirement is not clear do let me know.ThanksKaushik |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 09:03:45
|
| Definitely go with SQL Server 2000. Do you absolutely have to store the XML file in the database itself? Or does the XML file have a structure related to the database your storing it in (meaning, can the data in the XML file be converted to a relational database format?) BLOBs in SQL Server are stored in text/ntext/image columns, and the ability to extract and parse them is limited except for the XML functions, and these functions work best when parsing XML data into tables. It's a lot easier to store it that way, you can always reconstruct the XML document on the fly. |
 |
|
|
spock
Starting Member
35 Posts |
Posted - 2002-09-26 : 02:56:15
|
| Hi,I am allowed to store the data in the XML into RDBMS formats. But I would not be using the data in the RDBMS tables meaning i wont need to run queries on the tables. My component interacts with the other components in the system using XML messages.So i would always need to construct an XML. Would storing XML in RDBMS format give any advantages over storing them as blobs ?ThanksKaushik |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-26 : 08:11:21
|
BLOB support in SQL Server is OK, not bad, but not great either. It will have better support in Yukon, the next version of SQL Server, but that doesn't really help you now... One advantage of parsing the XML into an RDBMS structure is that it's already parsed, and you can query for particular nodes/elements much more easily than having to:-extract the entire XML document-traverse the elements and parse them-return elements to the application...every time you need only particular element(s). If you don't need to query elements and you will ALWAYS deal with the entire XML document, then it doesn't matter.Another advantage is that you can completely change the structure of the XML that is generated without changing the structure of the database tables.It's hard to say which is more advantageous without more details about your application, but I'd say you'd get a lot more flexibility, with very little overhead, if you parsed the XML files into the database and generated XML from them whenever needed. |
|
|
|
|
|
|
|
|
XML support