Tell me more ×
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.
up vote 1 down vote favorite

Is the best way to implement a large database is XML? Or is there a better way?

1 - reduce the time complexity
2 - Reduce the amount of traffic between the tables
And other factors ...

Which example is better, one or two?

1.Student Table: (without XML)
 id
 name
 family
 age
 address

2.Student Table With XML
 id
 XMLDT (contains name,family,age,address)
share|improve this question
columns with XML datatype – Qrosh Dec 16 '12 at 12:34
YES a table with two columns, 1.Id,2.Xml Column (name,age,address) – Qrosh Dec 16 '12 at 12:52
Ah OK I understand what you are asking now. I would always go for option 1 but I won't submit an answer as I've never really looked into XML storage or XML indexes. – Martin Smith Dec 16 '12 at 13:02
I know that the second approach is better in terms of security and encryption But I doubt the speed! The first approach automatically Convert to XMl by SQL Server to XML... – Qrosh Dec 16 '12 at 13:13
8  
My experience in building systems that extract data from systems using XML storage (Duck Creek in particular) is that it is fiddly and slow. They get away with it because insurance software typically has a requirement for flexibility, often has complex policy structures and usually has modest data volumes. If you want to store a large volume of data I would be hesitant to recommend that you use XML. – ConcernedOfTunbridgeWells Dec 16 '12 at 14:42
show 1 more comment

closed as not a real question by Martin Smith, Mark Storey-Smith, Mike Walsh, Derek Downey, RolandoMySQLDBA Dec 17 '12 at 21:04

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

2 Answers

up vote 2 down vote

There's no right or wrong answer to this without considering the requirements, but if you need to do anything except return to the client the entire XML blob -- based on some other criteria than the XML contents itself -- XML fields are not a good idea.

For searching, while XML indexes can mitigate some of the performance problems, there is a big storage penalty to pay for those. Since this question is tagged , this kind of solution (your option 2) is almost certainly inappropriate for all kinds of reasons.

The one thing you definitely want to do is shred the XML and put all commonly-used (searched, selected) attributes in relational fields. Depending on the structure of the XML, this could mean a single table, or it could mean multiple tables.

The only question is what to do with everything else that doesn't fit. The remaining fields could continue to live as XML if the schema has to be really flexible (probably a good idea to strip out everything else to save storage space). You could also consider implementing an architecture based on sparse columns (SQL Server 2008+) if the structure is more predictable.

If you need to put things back into XML for client applications, FOR XML will likely be your best friend.

share|improve this answer
up vote 1 down vote

How about making each of the elements of your XML a table or child-table and using in-built stored procedures to generate the XML from an SQL query result.

This provides you with the data consistency and validation of the relational model plus additional database functionality with the ability to generate your XML document on the fly. If you need the XML documents handy you can create the equivalent of a "reporting" table into which the XML is stored ...

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.