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

score 2 · Accepted Answer · answered May 22 '11 at 17:25

2

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)

answered May 22 '11 at 17:25

Mikael Eriksson

136,425
22
210
281

Sweet. That's it. Works like a charm! – lambacck May 22 '11 at 18:58

score 1 · Answer 2 · answered May 13 '11 at 14:33

1

Is this what you are looking for:

   SELECT NULLIF(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)

Using NULLIF ( expression , expression ) where the first expression is the expression returned from the data and the second is the expression to compare it to and if equal, it will return a NULL.

answered May 13 '11 at 14:33

CStroliaDavis

392
4
14

That was the solution in the linked to question that I said didn't work because I needed to distinguish between empty string / 0 and NULL. – lambacck May 15 '11 at 00:54
I apologize for missing that. I'm glad you got the answer you needed. – CStroliaDavis May 24 '11 at 18:22
As luck would have it, CStroliaDavis, your answer solved my problem perfectly :) Many thanks! – si618 Jun 18 '12 at 03:47

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

2 Answers2