title: Preferred way to access data within XML columns in SQL Server
body: <h1>Background</h1>
  
  <p>Recently I've started to use XML a lot more as a column in SQL Server 2005. During a bit of downtime yesterday, I noticed that two of the link tables I used a really just in the way and it bores me to tears having to write yet more supporting structure code for a couple of joins.</p>
  
  <p>To actually generate the data for these two link tables, I pass in two XML fields to my stored procedure, which writes the main record, breaks the two XML variables down into @tables and inserts them into the actual tables with the new <code>SCOPE_IDENTITY()</code> from the master record.</p>
  
  <p>After some though, I decided to just do away with those tables altogether and just store the XML in XML fields. Now I understand there are some pitfalls here, like general querying performance, <code>GROUP BY</code> doesn't work on XML data. And the query is generally a bit of a mess, but overall I like that I can now work with <code>XElement</code> when I get the data back.</p>
  
  <p>Also, this stuff isn't going to get changed. It's a one shot affair, so I don't have to worry about modification.</p>
  
  <p>I am wondering about the best way to actually get at this data. A lot of my queries involve getting a master record based upon the criteria of a child or even a subchild record. Most of the sprocs in the database do this but on a far more elaborate scale, usually requiring UDFs and Subqueries to work effectively but I have knocked up a trivial example to test querying some data...</p>
  
  <pre><code>INSERT INTO Customers VALUES ('Tom', '', '&lt;PhoneNumbers&gt;&lt;PhoneNumber Type="1" Value="01234 456789" /&gt;&lt;PhoneNumber Type="2" Value="01746 482954" /&gt;&lt;/PhoneNumbers&gt;')
  INSERT INTO Customers VALUES ('Andy', '', '&lt;PhoneNumbers&gt;&lt;PhoneNumber Type="2" Value="07948 598348" /&gt;&lt;/PhoneNumbers&gt;')
  INSERT INTO Customers VALUES ('Mike', '', '&lt;PhoneNumbers&gt;&lt;PhoneNumber Type="3" Value="02875 482945" /&gt;&lt;/PhoneNumbers&gt;')
  INSERT INTO Customers VALUES ('Steve', '', '&lt;PhoneNumbers&gt;&lt;/PhoneNumbers&gt;')
  </code></pre>
  
  <p>Now I can see two ways of grabbing it.</p>
  
  <h2>Method 1</h2>
  
  <pre><code>DECLARE @PhoneType INT
  SET  @PhoneType = 2
  
  SELECT ct.*
  FROM Customers ct
  WHERE ct.PhoneNumbers.exist('/PhoneNumbers/PhoneNumber[@Type=sql:variable("@PhoneType")]') = 1
  </code></pre>
  
  <p>Really? sql:variable feels a bit unwholesome. However, it does work. However it's distinctively more difficult to access data in a more meaningful way.</p>
  
  <h2>Method 2</h2>
  
  <pre><code>SELECT ct.*, pt.PhoneType
  FROM Customers ct
    CROSS APPLY ct.PhoneNumbers.nodes('/PhoneNumbers/PhoneNumber') AS nums(pn)
    INNER JOIN PhoneTypes pt ON pt.ID = nums.pn.value('./@Type[1]', 'int')
  WHERE nums.pn.value('./@Type[1]', 'int') = @PhoneType
  </code></pre>
  
  <p>This is more like it. Already I can easily expand it to do joins and all other good stuff. I've used <code>CROSS APPLY</code> before on a table valued function, and it was very good. The execution plan for this as opposed to the previous query is seriously more advanced. Admittedly I haven't done any indexing and whatnot on these tables, but it's 97% of the entire batch cost.</p>
  
  <h2>Method 2 (expanded)</h2>
  
  <pre><code>SELECT ct.ID, ct.CustomerName, ct.Notes, pt.PhoneType
  FROM Customers ct
    CROSS APPLY ct.PhoneNumbers.nodes('/PhoneNumbers/PhoneNumber') AS nums(pn)
    INNER JOIN PhoneTypes pt ON pt.ID = nums.pn.value('./@Type[1]', 'int')
  WHERE nums.pn.value('./@Type[1]', 'int') IN (SELECT ID FROM PhoneTypes)
  </code></pre>
  
  <p>Nice <code>IN</code> clause here. I can also do something like <code>pt.PhoneType = 'Work'</code></p>
  
  <h1>Finally</h1>
  
  <p>So I'm essentially obtaining the results that I want, but is there anything I should be aware of when using this mechanism to interrogate small amounts of XML data? Will it fall down on performance during elaborate searches? And is the storage of such markup style data too much of an overhead?</p>
  
  <h2>Side note</h2>
  
  <p>I've used things like <code>sp_xml_preparedocument</code> and <code>OPENXML</code> in the past just to pass lists into sprocs, but this is like a breath of fresh air in comparison!</p>