snippetsqlModerate

How to write this SQL query for a XML type?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thissqlquerywritexmltypeforhow

Problem

I am using SQL Server 2012 in which I have a table [abc] which has a column called [Information] of the [XML] type.

This column has values like -

How can I write a SQL Query which can query this column and further extract the value [Tid] ?

Solution

What you want here is not entirely clear from your question. My guess is that you want the value from the ` node where the 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 `.

declare @T table(X xml not null);
insert into @T(X) values
('
  
    
      
        
          Cid
          2323232323
        
        
          Tid
          6656565656
        
      
    
  
');

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);

Code Snippets

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

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);

Context

StackExchange Database Administrators Q#204736, answer score: 10

Revisions (0)

No revisions yet.