title: SQL Server Parse XML empty property
body: <p>I'm trying to parse / shred XML in sql server 2008 via TSQL, one of the nodes has a blank property in the following format  with no value specified.  I can't seem to retrieve that value and it throws off all the others (shifts the columns ?)</p>
  
  <p>Here is the tsql code and a pic of what is wrong:</p>
  
  <pre><code>declare @doc nvarchar(4000)
  
  set @doc = ' 
  &lt;Objects&gt;
    &lt;Object&gt;
      &lt;Property Name="Path"&gt;some path&lt;/Property&gt;
      &lt;Property Name="InstanceName"&gt;some instance&lt;/Property&gt;
      &lt;Property Name="result1"&gt;0.390630000000003&lt;/Property&gt;
      &lt;Property Name="result2"&gt;63345649697265&lt;/Property&gt;
    &lt;/Object&gt;
    &lt;Object&gt;
      &lt;Property Name="Path"&gt;another path&lt;/Property&gt;
      &lt;Property Name="InstanceName" /&gt;
      &lt;Property Name="result1"&gt;100&lt;/Property&gt;
      &lt;Property Name="result2"&gt;1002&lt;/Property&gt;
    &lt;/Object&gt;
   &lt;/Objects&gt;
  '
    SELECT 
   item.ref.value('(Property/text())[1]', 'nvarchar(128)') AS Path, 
   item.ref.value('(Property/text())[2]', 'nvarchar(128)') AS InstanceName,
   item.ref.value('(Property/text())[3]', 'nvarchar(128)') AS result1,
   item.ref.value('(Property/text())[4]', 'nvarchar(128)') AS result2
    FROM (SELECT CAST(@doc AS XML) AS feedXml) feeds(feedXml) 
     CROSS APPLY feedXml.nodes('/Objects/Object') AS item(ref)
  </code></pre>
  
  <p>When you run the query notice that the column InstanceName is populated with the value from the other column instead of blank, empty or null.</p>
  
  <p>Any help is appreciated.</p>