Antonio,

You might need to work with TYPED XML to achieve this. You need to create a schema collection and set the element to be "nillable". Then in your XML instance, you need to quality the element with "xsi:nil" attribute to indicate that the value of element is NULL.

Here is an example [replace square brackets with xml tags before you run it 🙂 ]

CREATE XML SCHEMA COLLECTION NullTest AS '

[xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"]

[xsd:element name="Employee"]

[xsd:complexType]

[xsd:sequence]

[xsd:element name="ContactInfo"]

[xsd:complexType]

[xsd:sequence]

[xsd:element name="FirstName" type="xsd:string"/]

[xsd:element name="LastName" nillable="true"

type="xsd:string"/]

[/xsd:sequence]

[/xsd:complexType]

[/xsd:element]

[/xsd:sequence]

[/xsd:complexType]

[/xsd:element]

[/xsd:schema]'

GO

DECLARE @x XML(NullTest)

SELECT @x = '

[Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"]

[ContactInfo]

[FirstName]Bob[/FirstName]

[LastName xsi:nil="true"/]

[/ContactInfo]

[/Employee]'

SELECT

x.value('(FirstName)[1]','varchar(30)') AS FirstName,

x.value('(LastName)[1]','varchar(30)') AS LastName

FROM @x.nodes('/Employee/ContactInfo') n(x)

/*

FirstName LastName

------------------------------ ------------------------------

Bob NULL

*/