title: In an SQL XQuery result set, is there a way to make the "value" operator return null on missing elements?
body: <p>When getting values out of xml attributes, using the xquery value operator, missing attributes are returned as null. Is there a way to do the same thing without resorting to <code>CASE WHEN N.exists('path')=0 THEN NULL ELSE n.value('path') END</code>?</p>
  
  <p>This SQL:</p>
  
  <pre><code>DECLARE @val xml
  SET @val = '&lt;records&gt;
    &lt;record attrval="attrval"&gt;
      &lt;stringvalue&gt;some value&lt;/stringvalue&gt;
      &lt;intvalue&gt;1&lt;/intvalue&gt;
    &lt;/record&gt;
    &lt;record&gt;
      &lt;intvalue&gt;1&lt;/intvalue&gt;
    &lt;/record&gt;
    &lt;record&gt;
      &lt;stringvalue&gt;another value&lt;/stringvalue&gt;
    &lt;/record&gt;
  &lt;/records&gt;'
  
  SELECT N.query('stringvalue').value('/', 'varchar(100)') AS stringvalue,
         N.query('intvalue').value('/', 'int') AS intvalue,
         N.value('@attrval', 'varchar(100)') AS attrval
  FROM @val.nodes('//record') as T(N)
  </code></pre>
  
  <p>Results in:</p>
  
  <pre><code>[stringvalue]       [intvalue]  [attrval]
  some value          1           attrval
                      1           NULL
  another value       0           NULL
  </code></pre>
  
  <p>And I would like to get:</p>
  
  <pre><code>[stringvalue]       [intvalue]  [attrval]
  some value          1           attrval
  NULL                1           NULL
  another value       NULL        NULL
  </code></pre>
  
  <p>Without doing:</p>
  
  <pre><code>SELECT CASE WHEN N.exists('stringvalue')=1 THEN N.query('stringvalue').value('/', 'varchar(100)') ELSE NULL END AS stringvalue,
         CASE WHEN N.exists('intvalue')=1 THEN N.query('intvalue').value('/', 'int') ELSE NULL END AS intvalue,
         N.value('@attrval', 'varchar(100)') AS attrval
  FROM @val.nodes('//record') as T(N)
  </code></pre>
  
  <p>Note that in this case I can't just use attribute values because there is a limit on the length of the attributes imposed by SQL Server and some of my data exceeds that.</p>
  
  <p>Also, there is a related question but the answer is not applicable because I need to distinguish between empty and missing: <a href="https://stackoverflow.com/questions/1868385/sql-server-xquery-return-null-instead-of-empty">SQL Server xQuery return NULL instead of empty</a></p>