T-SQL 2005: Passing Null Values through XML dataty

问题:

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 would I go about modifying this so that if a blank value is entered in for node c, the value should be NULL, not the default of int (0)?

回答1:

In case anyone's wondering, I went this route, although I was hoping there was a way to do it via the XML features of SQL server:

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

回答2:

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