Getting the data of Ancestor node + XQuery-Sql <p>This is how my XML looks like : </p> <pre><code> &lt;Product sequence_number="1" number="1543448904" id="S1" unit_number="1"&gt; &lt;consumer_narrative name="GLENN,GREGORY" date_filed="02/13/2009"&gt; &lt;message type="Consumer Comments"&gt;THE CONSUMER STATES THIS WAS NOT &lt;/message&gt; &lt;message type="Consumer Comments"&gt;THE PRODUCT REQUESTED.&lt;/message&gt; &lt;/consumer_narrative&gt;&lt;/Product&gt; &lt;Product sequence_number="2" number="1543448905" id="S1" unit_number="1"&gt; &lt;consumer_narrative name="JOHN,GORDON" date_filed="08/23/2009"&gt; &lt;message type="Consumer Comments"&gt;THE CONSUMER STATES THAT&lt;/message&gt; &lt;message type="Consumer Comments"&gt;WRONG PRODUCT WAS SENT.&lt;/message&gt; &lt;/consumer_narrative&gt; &lt;/Product&gt; </code></pre> <p>My Query : </p> <pre><code>SELECT tab.col.value('../@number', 'varchar(30)') [Claim Number], tab.col.value('../@name', 'varchar(30)') [Name], tab.col.value('../@date_filed', 'varchar(30)') [DateField], tab.col.value('@type', 'varchar(50)') [Type], tab.col.value('.', 'varchar(250)') [CustomerComments] FROM XMLTABLE AS B CROSS APPLY xmldocument.nodes('//Product/consumer_narrative/message') tab(col) WHERE B.XMLId = 123 </code></pre> <p>Gives me "null" for Claim Number. What should I have in the place of ../@number to get the claim number.</p>