T-SQL 2005: Passing Null Values ​​Through an XML Data Type

Question

For the following query:

DECLARE @ItemInfo xml

SET @ItemInfo = '<is><i><a>A Value</a><b>B Value</b><c></c></i></is>'

SET ARITHABORT ON

SELECT
    Params.Item.query('a').value('.', 'varchar(150)')
    ,Params.Item.query('b').value('.', 'varchar(150)')
    ,Params.Item.query('c').value('.', 'int')
FROM 
    @ItemInfo.nodes('/is/i') as Params(Item)

How can I change this so that if a blank value is entered for node c, the value should be NULL, and not the default value for int (0)?

Answer 1

In case someone wonders, I went along this route, although I was hoping there was a way to do this through the SQL functions of the SQL server:

CAST(NULLIF(Params.Item.query('c').value('.', 'varchar(100)'), '') AS int)

Answer 2

cast (nullif (Params.Item.query ('c'). value ('.', 'varchar (150)'), '') as int)