Is there any way that you could search for a value in any nodes within your xml data type?

Hi everyone,

I was wondering if there is any way that you could search for a value in any nodes within your xml data type without actually knowing what nodes you have or how many there are.

Please provide sample query for it if possible. Thanks.

declare @.x xml

set @.x = '<root><x>Test1</x><y>Test2<z>Test3</z></y></root>'

select @.x.query('//*[text()="Test1"]')

select @.x.query('//*[text()="Test2"]')

select @.x.query('//*[text()="Test3"]')

Results:

<x>Test1</x>

(1 row(s) affected)

-

<y>Test2<z>Test3</z></y>

(1 row(s) affected)

--

<z>Test3</z>

(1 row(s) affected)

|||

An open ended querying of xml that has some "value" isn't trivial. For example, if your xml is semi-structured and has mixed nodes, the above query might return results you didn't expect. The following query will return the element foo. That is because foo has two text() nodes and the = is the existential operator. Again, this could be what you want anyway. Also, this query doesn't find the attribute bar.

select convert(xml, '<foo bar="abc">abc<bar/>abc</foo>').query('//*[text() = "abc"]')

However, depending on the structure of you XML, this might be fine.

If you want to include elements who's attributes match the target value, you can do the following:

select convert(xml, '<foo bar="abc"/> <baz>abc</baz> <foo bar="not"/>').query('//*[text() = "abc" or @.* = "abc"]')

Regards,

Galex