I agree with the 2 previous answers.
I think the real answer is: it depends. What are you going to DO with the data in the future?
Are you going to dig up one of these XML records once a month to satisfy some reporting requirement, or are 20 people a day going to be doing something that requires that you find all the XML records with first name XYZ and DOB before 1990?
In the first case, yeah, XML is fine and easy.
In the second case, NO!!! You want to do that slice and dice with SQL.
Your case probably falls in between. If it isn’t clear cut, I would err on the side of not storing XML. Might be more work up front, but you’ll have an easier time meeting future requirements for slicing and dicing the data.
It’s not necessary. The database is already a mechanism for handling data. A relational database like SQL server has a very optimized mechanism for storing and manipulating data.
XML is most useful for transferring data. You can have your database program export a database or query result as XML, and then even a language without direct data access (like JavaScript) can manipulate it.
You can store whatever you like in a database. That’s what they are for. More important is how you index it, combine it was other tables, and arrange the data or choose the database to suit the kind of performance you need. Some databases are optimized to read quickly and as a side effect often update slowly (DNS is a good example), and others are more suited to data that changes quickly.
I think it is pretty bold for me to assume that I know the reason you have decided to store xml in a database. It being bad practice or not depends on why you have chosen to do it and what your alternatives are… where the data needs to go, what it holds, and how complex the structure is.
If your structure is complex and the xml you are storing doesn’t have a consistant structure, then maybe you do want to just store it as is in a database.
If your structure is consistant and simple, it might make more sense to disasemble the xml and dump the data into a similarly formatted table, and then re-assemble it into xml once you’re ready to transport the data. It will save you overhead.
Bad practice from a database design standpoint, or bad practice from a database engine performance standpoint?
I suppose the answer to the performance issue depends on the database engine. Most relational engines have a fixed page size (in SQL Server it’s 8k) and if you want to store a document longer than that you’ll have to use a have to use an arbitrary length column type like NTEXT. Typically the arbitrary length fields store actual data outside of the database (pretty much as individual files on disk). Getting these files connected to the rest of the row data when selecting can be a performance issue.
Starting with SQL Server 2000 there was some support for XML (such as returning data as XML) but overall the extensions for working with XML were a big letdown. Native XML support has improved with SQL 2005 and 2008, but I haven’t revisited it after my experience with 2000.
Like several others stated, it depends a lot on what you plan to do with it. If you simply need the data in XML format for porting somewhere, you can always run a SQL query to create a subtable, then export that table in XML format.
I agree with the 2 previous answers.
I think the real answer is: it depends. What are you going to DO with the data in the future?
Are you going to dig up one of these XML records once a month to satisfy some reporting requirement, or are 20 people a day going to be doing something that requires that you find all the XML records with first name XYZ and DOB before 1990?
In the first case, yeah, XML is fine and easy.
In the second case, NO!!! You want to do that slice and dice with SQL.
Your case probably falls in between. If it isn’t clear cut, I would err on the side of not storing XML. Might be more work up front, but you’ll have an easier time meeting future requirements for slicing and dicing the data.
It’s not necessary. The database is already a mechanism for handling data. A relational database like SQL server has a very optimized mechanism for storing and manipulating data.
XML is most useful for transferring data. You can have your database program export a database or query result as XML, and then even a language without direct data access (like JavaScript) can manipulate it.
You can store whatever you like in a database. That’s what they are for. More important is how you index it, combine it was other tables, and arrange the data or choose the database to suit the kind of performance you need. Some databases are optimized to read quickly and as a side effect often update slowly (DNS is a good example), and others are more suited to data that changes quickly.
I think it is pretty bold for me to assume that I know the reason you have decided to store xml in a database. It being bad practice or not depends on why you have chosen to do it and what your alternatives are… where the data needs to go, what it holds, and how complex the structure is.
If your structure is complex and the xml you are storing doesn’t have a consistant structure, then maybe you do want to just store it as is in a database.
If your structure is consistant and simple, it might make more sense to disasemble the xml and dump the data into a similarly formatted table, and then re-assemble it into xml once you’re ready to transport the data. It will save you overhead.
Those are my thoughts.
Bad practice from a database design standpoint, or bad practice from a database engine performance standpoint?
I suppose the answer to the performance issue depends on the database engine. Most relational engines have a fixed page size (in SQL Server it’s 8k) and if you want to store a document longer than that you’ll have to use a have to use an arbitrary length column type like NTEXT. Typically the arbitrary length fields store actual data outside of the database (pretty much as individual files on disk). Getting these files connected to the rest of the row data when selecting can be a performance issue.
Starting with SQL Server 2000 there was some support for XML (such as returning data as XML) but overall the extensions for working with XML were a big letdown. Native XML support has improved with SQL 2005 and 2008, but I haven’t revisited it after my experience with 2000.
Like several others stated, it depends a lot on what you plan to do with it. If you simply need the data in XML format for porting somewhere, you can always run a SQL query to create a subtable, then export that table in XML format.