Absolutely correct, and I would like to add my 2 cents about the inner text treatment. Inner text is treated in exact same fashion. If you would like the statement to return null values, just don't provide the node at all and SQL Server will deal with it just fine as well. For example, the xml sample below has 2 "records". First record does not have the node named Gender and the second record does not have the ID attribute. This means that the first record will have null for Gender column and second - null for ID column. I believe that this is the simplest way to deal with nulls, specifically because the out of the box minOccurences of any node or attribute is zero anyway.

declare @xml xml;

set @xml = '

<rows>

<r ID="1">

<LastName>Clown</LastName>

<FirstName>Bozo</FirstName>

<MiscInfo>Bozo is a clown at Wringley</MiscInfo>

</r>

<r>

<LastName>Netchaev</LastName>

<FirstName>Oleg</FirstName>

<Gender>Male</Gender>

<MiscInfo>Oleg''s info goes here</MiscInfo>

</r>

</rows>

';

select

item.value('@ID', 'int') ID,

item.value('LastName[1]', 'varchar(10)') LastName,

item.value('FirstName[1]', 'varchar(10)') FirstName,

item.value('Gender[1]', 'varchar(10)') Gender,

item.value('MiscInfo[1]', 'varchar(35)') MiscInfo

from @xml.nodes('//rows/r') R(item);

The above returns expected output:

ID LastName FirstName Gender MiscInfo

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

1 Clown Bozo NULL Bozo is a clown at Wringley

NULL Netchaev Oleg Male Oleg's info goes here

Very good article, thank you Richard. It is great to learn about different methods to achieve the same result.

Oleg