How to return null when element is null

Question

I'm converting an xml string to a table type:

SELECT
[Guid] = r.value('(Guid)[1]', 'uniqueidentifier'),
[Id] = r.value('(Id)[1]', 'int'),
[SomeColumn] = r.value('(SomeColumn)[1]', 'int'),
FROM
@xml.nodes('//Items') AS T(r)

Where SomeColumn is a nullable int. The xml that is passed is the following for this element:

<SomeColumn xsi:nil="true" />

However, the column is set to 0 instead of null. Is there a way to enforce to return null on nullable columns that are actually null?

Answer 1

Well, you can retrieve it as varchar instead of int and then check for emptiness of retrieved string something like:

SELECT
    [Guid] = r.value('(Guid)[1]', 'uniqueidentifier'),
    [Id] = r.value('(Id)[1]', 'int'),
    [SomeColumn] = cast(nullif(r.value('(SomeColumn)[1]', 'varchar(20)'), '') as int)
FROM
    @xml.nodes('//Items') AS T(r)