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?

Can we have a minimal but complete sample piece of XML that the above query can be run against please? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 9, 2017 at 13:08

Andrey Korneyev · Accepted Answer · 2017-03-09 13:09:33Z

3

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)

answered Mar 9, 2017 at 13:09

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return null when element is null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related