check for value before

Question

DECLARE @xVar XML
SET @xVar = 
  '<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
   <title>Writing Secure Code</title>
   <author>
      <firstname>Michael</firstname>
      <lastname>Howard</lastname>
      <age>25</age>
      <birthday>2010-05-17T00:00:00</birthday>
   </author>
   <author>
      <firstname></firstname>
      <lastname>LeBlanc</lastname>
      <age></age>
      <birthday></birthday>
   </author>
   <price>39.99</price>
   </book>'

SELECT nref.query('age') AS age
FROM @xVar.nodes('//author') AS authors(nref)

How can I check for an actual value in a node before parsing it into a column. When inserting into a normal table this code will insert 0 for the age when what I really need is null. Default values are inserted for any non text datatype like int or datetime(1/1/1900 inserted).

Answer 1

You can use the built-in function NULLIF to do this:

SELECT NULLIF(nref.query('age'), 0) AS age
FROM ...

If the first term matches the second term, the function returns NULL. If not then it returns the first term.