title: SQL STUFF() with UNION Query returning NULL
body: <p>I have a SQL Server 2012 query that is returning 1 row of data , BUT could return multiple rows, based on the query. </p>
  
  <p>I want to turn the following data into a csv concatenated string. My query uses <code>STUFF()</code> with a <code>UNION</code> query which I based off <a href="https://stackoverflow.com/questions/9165486/using-union-all-in-stuff-xml-path">this Stack Overflow question</a>.</p>
  
  <p>i.e: my data would be this</p>
  
  <pre><code>MR
  ------
  NS
  ------
  </code></pre>
  
  <p>and would become:</p>
  
  <pre><code>MR, NS
  </code></pre>
  
  <p>When I select the data without the <code>STUFF()</code> part of the query, it returns data no problem.</p>
  
  <p>The full query (see below) runs without errors but always returns <code>NULL</code> even though the <code>SELECT UNION</code> part of the query returns data.</p>
  
  <p>My full query looks like this:</p>
  
  <pre><code>SELECT STUFF((Select * FROM (
  SELECT ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
  FROM personenc INNER JOIN contacts ON personenc.cntid = contacts.cntid INNER JOIN scddisc ON contacts.discipline = scddisc.disid
  WHERE (personenc.perid = 15410) AND (scddisc.active = 1) AND (contacts.active = 1) AND (scddisc.medgroup = 1) --AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')
  UNION 
  SELECT ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
  FROM scddisc INNER JOIN contacts ON scddisc.disid = contacts.discipline INNER JOIN personenc ON contacts.cntid = personenc.ocntid
  WHERE (personenc.perid = 15410) AND (scddisc.active = 1) AND (contacts.active = 1) AND (scddisc.medgroup = 1) -- AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')
  ) AS k
  FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,'')
  </code></pre>
  
  <p>Can anyone let me know what I have done wrong?</p>
  
  <p>The query that works is the <code>SELECT UNION</code> part of the above query
  i.e. when I run the query below it returns valid data.</p>
  
  <pre><code>SELECT ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
      FROM personenc INNER JOIN contacts ON personenc.cntid = contacts.cntid INNER JOIN scddisc ON contacts.discipline = scddisc.disid
      WHERE (personenc.perid = 15410) AND (scddisc.active = 1) AND (contacts.active = 1) AND (scddisc.medgroup = 1) --AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')
      UNION 
      SELECT ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
      FROM scddisc INNER JOIN contacts ON scddisc.disid = contacts.discipline INNER JOIN personenc ON contacts.cntid = personenc.ocntid
      WHERE (personenc.perid = 15410) AND (scddisc.active = 1) AND (contacts.active = 1) AND (scddisc.medgroup = 1) -- AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')
  </code></pre>
  
  <p>However when combined with the <code>FOR XML</code> and <code>STUFF</code>, then the full statement return <code>NULL</code> </p>