Querying XML column

1

Hi all,

I was wondering if you can assist me with querying an xml column?

I have a table called Activity which contains xml data in a field called sqltext in the following format

<?query -- select * from users -- ?>

How can I query this sqltext column using a like statement.
I would like to run a select statement which looks for the word 'users' in this sqltext field.

Many Thanks,

Ranvir

2

You can do this with the LIKE statement:

SELECT sqltext
FROM
Activity
WHERE sqltext LIKE '%users%';

3

But the sqltext column is an xml field, i want to query it using a like statement but that is not allowed with an xml data type.

4

You can always convert, it's the most easy way:

SELECT sqltext
FROM
Activity
WHERE CONVERT(VARCHAR(MAX),sqltext) LIKE '%users%';

5

The example you have shown is a little strange because you have invalid text in the XML declarations section. That is likely to be ignored by any xml parser. Usually it is things like encoding, XML version etc. that appears in the prologue. Search for XML declarations examples.

If you did have valid xml here are a few ways to query:

CREATE TABLE #tmp (x XML);
GO
INSERT INTO #tmp 
SELECT '<element1>SomeData</element1>' UNION ALL
SELECT '<element2>SomeOtherData</element2>' UNION ALL
SELECT '<element3>SomeData</element3>' UNION ALL
SELECT '<element1>SomeOthervalue</element1>' 
GI
-- if you want to select all nodes at the top level that have 
-- a node named element1
SELECT * 
FROM
    #tmp
WHERE
    x.exist('element1') = 1

-- ify ouwant to select all nodes that have the value
-- SomeData at the top level.
SELECT *
FROM
    #tmp
WHERE
    x.exist('*[.="SomeData"]') = 1

-- if you want all nodes that had values that contain
-- the string "Other"
SELECT *
FROM
    #tmp
WHERE
    x.value('.','VARCHAR(MAX)') LIKE '%Other%'
1 Like
6

Many thanks for this, it did the trick!