title: Get XML node values using SQL
body: <p>I'm using SQL Server 2014 and I need help with constructing a query that will give me all of the values for a particular XML node. I have a table that has information stored in it in xml. The xml data is like below:</p>
  
  <pre><code>&lt;category&gt;
     &lt;text&gt;Laser Printers&lt;/text&gt;
     &lt;title id="1"&gt;
        &lt;text&gt;HP OfficeJet Pro 8610&lt;/text&gt;
     &lt;/text&gt;
     &lt;title id="2"&gt;
        &lt;text&gt;HP OfficeJet Pro 8700&lt;/text&gt;
     &lt;/text&gt;
     &lt;title id="3"&gt;
        &lt;text&gt;Canon PIXMA MX 922&lt;/text&gt;
     &lt;/text&gt;       
  &lt;/category&gt;
  </code></pre>
  
  <p>I'm only able to get the value from the first node for each row in the table using this query.</p>
  
  <pre><code>SELECT it.contents.value('(category/title/text)[1]', 'NVARCHAR(255)') as Printer
  FROM inventory i
  INNER JOIN store_products p ON i.inventoryId = p.inventoryId
  INNER JOIN items it ON p.itemId = it.itemId
  </code></pre>
  
  <p>So my result is:</p>
  
  <pre><code>Printer
  HP OfficeJet Pro 8610
  </code></pre>
  
  <p>What I need is the following:</p>
  
  <pre><code>Printer
  HP OfficeJet Pro 8610, HP OfficeJet Pro 8700, Canon PIXMA MX 922
  </code></pre>
  
  <p>Is this even possible?</p>