title: T-SQL add delimiter between data when using OPENROWSET and XML
body: <p>I'm trying to add a delimiter between the data that is being combined in a single column and having a lot of trouble trying to do so.</p>
  
  <p>I'm working from a large XML file (8+ megs) and trying to get pieces of the data combined down to single columns for my database. The below query is doing this (sort of) but there is no separation of the data currently.</p>
  
  <pre><code>WITH XmlFile (Contents) AS (
  SELECT CONVERT (XML, BulkColumn) 
  FROM OPENROWSET (BULK 'C:\test.xml', SINGLE_BLOB) AS XmlData
  )
  SELECT c.value('(name)[1]', 'varchar(max)') AS [Name],
          c.value('(stuff)[1]', 'varchar(max)') AS [Stuff]       
  FROM   XmlFile CROSS APPLY Contents.nodes ('(/root/item)') AS t(c)
  </code></pre>
  
  <p><strong>test.xml file:</strong></p>
  
  <pre><code>&lt;root&gt;
  &lt;item&gt;
      &lt;name&gt;Something&lt;/name&gt;
      &lt;stuff&gt;
          &lt;note&gt;Test 123&lt;/note&gt;
          &lt;note&gt;Another Test&lt;/note&gt;
      &lt;/stuff&gt;
  &lt;/item&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p><strong>What it's returning:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/cs4DQ.png" alt="enter image description here"></p>
  
  <p>Which is almost what I want! both note nodes have been merged into a single column... But I can't figure out howto add a delimiter between the values "Test 123 <strong>,</strong> Another Test"</p>
  
  <p>Any help would be greatly appreciated!</p>