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)

2.Student Table With XML
 XMLDT (contains name,family,age,address)
  • columns with XML datatype – coditori Dec 16 '12 at 12:34
  • YES a table with two columns, 1.Id,2.Xml Column (name,age,address) – coditori 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... – coditori 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

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.

| improve this answer | |

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 ...

| improve this answer | |

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