Querying XML column in SQL Server

Question

in an column of xml data type, that contain the following structure, i want to find table rows where v like '%a%'

<PostVar>
  <d n="A" v="abc" />
  <d n="B" v="def" />
  <d n="C" v="ghi" />
  <d n="D" v="jkl" />
  <d n="E" v="mno" />
  <d n="F" v="pqr" />
</PostVar>

i used to work with a different xml type :-

<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2011-09-28T11:47:52.597</PostTime>
  <SPID>105</SPID>
  <ServerName>srv</ServerName>
  <LoginName>sa</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>server</DatabaseName>
  <SchemaName>tim</SchemaName>
  <ObjectName>_SystemTableVersion</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE tim._SystemTableVersion ADD
resr datetime NULL
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

and SQL i use to "extract" data is :-

SELECT
    [ID]
    ,[EventData].value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
    , ServerName, ServiceName, RemServer, SUserName, HostName, UserName, SUserSName, SystemUser, SessionUser, OriginalLogin, RecTime
FROM
    dbo.myTable
WHERE
    [EventData].value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
        LIKE '%wordImLookingFor%'

any help would be appreciated.

Mikael Eriksson · Accepted Answer · 2014-02-24 14:17:13Z

1

You can use exist() and contains().

where XMLCol.exist('/PostVar/d/@v[contains(., "a")]') = 1

The comparison is however always case sensitive.

If you need to check against a variable you should use sql:variable().

declare @v varchar(10)
set @v = 'a'

select *
from YourTable
where XMLCol.exist('/PostVar/d/@v[contains(., sql:variable("@v"))]') = 1

answered Feb 24, 2014 at 14:17

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

armen Over a year ago

i know this could be a new question, but my next move is built on the previous data set. the question is : how to SELECT v FROM MyTable WHERE n='D';, and the expected result is jkl

Mikael Eriksson Over a year ago

@armen

select P.X.value('@v', 'varchar(100)') as v from @T as T   cross apply T.XMLCol.nodes('/PostVar/d') as P(X) where P.X.exist('@n[. = "D"]') = 1

Mikael Eriksson Over a year ago

@armen Or

select P.X.value('@v', 'varchar(100)') as v from @T as T   cross apply T.XMLCol.nodes('/PostVar/d') as P(X) where P.X.value('@n', 'varchar(100)') = 'D'

Seymour · Accepted Answer · 2014-02-24 11:47:28Z

0

I think the following will help:

select * -- your select list here  
  from mySchema.myTable -- your schema and table name here  
    where myColumn.value('(/PostVar/d/@v)[1]', 'nvarchar(3)') Like '%a%'

Note: Since you did not specify the definition for the new table, you need to replace mySchema, myTable and myColumn to match your scenario.

The following link provides a good reference:
http://technet.microsoft.com/en-us/library/ms178030.aspx

Regards.

answered Feb 24, 2014 at 11:47

Seymour

7,06513 gold badges47 silver badges54 bronze badges

1 Comment

armen Over a year ago

this scenario return rows only for the case where v like '%a%', but in case of where v like '%e%' it does not return rows - except if i change '(/PostVar/d/@v)[1]' to '(/PostVar/d/@v)[2]'. the problem is xml is dynamic, and the number of <d ... /d> is unknown.

Collectives™ on Stack Overflow

Querying XML column in SQL Server

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related