0

What's the best way to select null from empty XML Node ?

E.g.

DECLARE @xml xml = N'<t />'
SELECT @xml.value('(t)[1]','int')

This will return 0 instead of NULL. Why ? How to extract null?

michał kudanowski
  • 33
  • 6

2 Answers2

3

An empty element does not have a text(). Try this:

DECLARE @xml xml = N'<t />'
SELECT @xml.value('(t/text())[1]','int');

UPDATE Some explanation

XML uses a defined string format for each datatype. As you surely know, an INT is not stored as a chain of digits, but as a binary value internally. What you see in a resultset and what is generated into an XML is a string representation for human readers following certain rules to ensure correct reading. Date and time values should be ISO8601, BLOBs are converted to base64 and so on...

Each datatype has a default value, with INT this is 0. Try this, it's the same without XML:

SELECT CAST('' AS INT)     --returns 0
      ,CAST(NULL AS INT)   --returns NULL

There is no NULL value in XML (well, you can specify XSINIL to use a marking attribute), but elements can be missing. Only missing elements are read as NULL...

Check this answer for some examples about NULL and empty. Check this answer to understand more about text()

Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

I found also this function:

DECLARE @xml xml = N'<t />'
SELECT @xml.query('/t').value('number(.)','int')

But I was wondering if there is, a way without using inner function. Maybe some namespace.

michał kudanowski
  • 33
  • 6
  • You are thinking to complicated... No need for `query()`, this does not help at all and has nothing to do with the issue... You'd get the same with `SELECT @xml.value('number(.)','int')` or with `SELECT @xml.value('number(/t[1])','int')`. This is bound to the usage of `number()`. Please read my updated answer for further details. – Shnugo May 31 '17 at 06:27