Mistake #9: treating relational storage as the automatic best practice. Native XML stores an XML document as XML instead of decomposing it into relational structures. That choice is especially useful when another system already supplies XML and the data must later be sent onward in XML. section 3.4
Total Warehouse Jobs and MagicChoc
Total Warehouse Jobs sends MagicChoc an employee’s previous contracts after a new contract is signed. The warehouse application uses that document daily, while an ETL process must deliver the data in XML format. Storing the document natively avoids transforming it merely to transform it back. section 3.4
A measured comparison showed that inserting and returning the native XML took about 34 ms end to end, with the return query taking less than 1 ms. The safer rule is not “always shred” or “always store XML”: choose according to read and write patterns. section 3.4.3
Use this decision checklist:
- Store data in native XML when it is written frequently but queried rarely. section 3.4.3
- Consider shredding when the application frequently queries the data, because T-SQL is generally more efficient than XQuery. section 3.4.3
- Do not reject XML because it is extensible, human readable, schema-validatable, and broadly processable. section 3.4
Listing 3.18 Adding a PreviousContracts column to the Employees table
ALTER TABLE dbo.Employees ADD PreviousContracts XML NULL ;
Add a nullable XML column for documents such as previous contracts. section 3.4.3
