How to write this SQL query for a XML type?
What you want here is not entirely clear from your question. My guess is that you want the value from the &lt;Value&gt; node where the &lt;Name&gt; node is Tid.

Shred the XML using the nodes() function on /Information/Groups/Group/Items/Item and add a predicate [Name/text()="Tid"] to check the value for &lt;Name&gt;.

declare @T table(X xml not null);
insert into @T(X) values
('&lt;Information&gt;
  &lt;Groups&gt;
    &lt;Group Name="Monitor"&gt;
      &lt;Items&gt;
        &lt;Item&gt;
          &lt;Name&gt;Cid&lt;/Name&gt;
          &lt;Value&gt;2323232323&lt;/Value&gt;
        &lt;/Item&gt;
        &lt;Item&gt;
          &lt;Name&gt;Tid&lt;/Name&gt;
          &lt;Value&gt;6656565656&lt;/Value&gt;
        &lt;/Item&gt;
      &lt;/Items&gt;
    &lt;/Group&gt;
  &lt;/Groups&gt;
&lt;/Information&gt;');

select I.X.value('(Value/text())[1]', 'bigint') as Value
from @T as T
  cross apply T.X.nodes('/Information/Groups/Group/Items/Item[Name/text()="Tid"]') as I(X);