6

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 -

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

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

  • 1
    The ‘extract the value [Tid]’ bit is confusing. What is it that you want to return, the string Tid or the contents of the Value tag where Name is Tid? – Andriy M Apr 24 '18 at 9:25
10

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

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

If you only need <Name>Tid</Name>, your query would look something like this:

DECLARE @x XML = N'
<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>
';

DECLARE @x_table TABLE (x XML);
INSERT @x_table ( x )
VALUES ( @x );

SELECT      ca.c.value('.', 'NVARCHAR(3)')
FROM        @x_table AS x
CROSS APPLY x.x.nodes('/Information/Groups/Group/Items/Item/Name/text()') AS ca(c)
WHERE       ca.c.exist('. [.="Tid"]') = 1;

If you need something else, let me know.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.