T-SQL 2005：通过XML数据类型传递空值

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)?

我将如何修改它,以便如果为节点c输入空值,则该值应为NULL,而不是默认的int(0)?

2 个解决方案

#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:

如果有人想知道,我走了这条路线,虽然我希望有办法通过SQL服务器的XML功能来做到这一点:

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

#2

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

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

#1