title: Sql Server Xml Column Best Practices
body: <p>What are the best practices for working with Sql server Xml columns to ensure quick performance and also ease of reporting?</p>
  
  <p>How do you set up the column? 
  do you leave it as untyped? 
  or associate it with a schema?</p>
  
  <p>Does associating the xml column with a schema improve query performance?</p>
  
  <p>Our use of xml columns is as follows:</p>
  
  <p>A.> On a PER customer basis we can define flexible storage of their data without overhauling our db.</p>
  
  <p>B.> We need to build reporting views for each customer which returns their data as if it was a simple table (for crystal reports or Sql Server Reporting Services).</p>
  
  <p>The syntax we currently use to query is as follows:</p>
  
  <pre><code>SELECT 
  Id, 
  doc.value('@associatedId','nvarchar(40)') as AssocId,
  doc.value('@name1', 'nvarchar(255)') as Name1,
  doc.value('@name2', 'nvarchar(255)') as Name2,
  doc.value('@name3', 'nvarchar(255)') as Name3,
  doc.value('@number', 'nvarchar(255)') as Number
  From OrderDetails
  CROSS APPLY OrderDetails.XmlData.nodes('//root/reviewers/reviewer') as XmlTable(doc)
  </code></pre>
  
  <p>Is there a quicker way to do this?  this query runs slowly for us in a table with 1million records, but only 800 currently have xml data!</p>
  
  <p>Thanks</p>
  
  <p>Pete</p>