In an SQL XQuery result set, is there a way to make the "value" operator return null on missing elements?

Question

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 CASE WHEN N.exists('path')=0 THEN NULL ELSE n.value('path') END?

This SQL:

DECLARE @val xml
SET @val = '<records>
  <record attrval="attrval">
    <stringvalue>some value</stringvalue>
    <intvalue>1</intvalue>
  </record>
  <record>
    <intvalue>1</intvalue>
  </record>
  <record>
    <stringvalue>another value</stringvalue>
  </record>
</records>'

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)

Results in:

[stringvalue]       [intvalue]  [attrval]
some value          1           attrval
                    1           NULL
another value       0           NULL

And I would like to get:

[stringvalue]       [intvalue]  [attrval]
some value          1           attrval
NULL                1           NULL
another value       NULL        NULL

Without doing:

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)

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.

Also, there is a related question but the answer is not applicable because I need to distinguish between empty and missing: SQL Server xQuery return NULL instead of empty

Answer 1

SELECT N.value('stringvalue[1]', 'varchar(100)') AS stringvalue,
       N.value('intvalue[1]', 'int') AS intvalue,
       N.value('@attrval', 'varchar(100)') AS attrval
FROM @val.nodes('//record') as T(N)