I've taken a look and wasn't able to find an answer that would help me with my issue. (Most probably due to my poor skills)
However was hoping that someone would be able to point me in the right direction.
The issue: I have an XML column in the table that I am querying and I need the query to return rows all rows with a specific value.
An example from the xml column
<EventD xmlns="http://example1" xmlns:e3q1="http://example2" xmlns:xsi="http://example3" xsi:type="e3q1:Valuechange">
<e3q1:NewValue>Running</e3q1:NewValue>
<e3q1:OldValue>Stopped</e3q1:OldValue>
</EventD>
What I would need to do is to return all rows that have "NewValue" as "Running"
;WITH XMLNAMESPACES ('example2' as e3q1)
select top 100
Xml.value('(EventD/NewValue)[1]', 'varchar(100)'),
* from Table1
and Xml.value('(EventD/NewValue)[1]', 'varchar(100)') like 'Running'
Yet this does not seem to return any rows at all, would be really grateful if someone could point out what am i doing wrong here.
Thanks in advance,
You do declare the namespace e3q1 (although it's missing the http:// and you don't use it later...), but you did not declare the default namespace
DECLARE @tbl TABLE([Xml] XML);
INSERT INTO @tbl VALUES
(
N'<EventD xmlns="http://example1" xmlns:e3q1="http://example2" xmlns:xsi="http://example3" xsi:type="e3q1:Valuechange">
<e3q1:NewValue>Running</e3q1:NewValue>
<e3q1:OldValue>Stopped</e3q1:OldValue>
</EventD>'
);
;WITH XMLNAMESPACES (DEFAULT 'http://example1', 'http://example2' as e3q1)
SELECT [Xml].value('(EventD/e3q1:NewValue)[1]', 'varchar(100)')
from @tbl AS Table1
WHERE Xml.value('(EventD/e3q1:NewValue)[1]', 'varchar(100)') like 'Running';
But this approach is - at least I think so - not what you really want. I think you are looking for .nodes(). The next lines show as alternative an approach to replace namespaces with a wildcard. But I would recommend to be as specific as possible.
SELECT Only.Running.value('text()[1]', 'varchar(100)')
from @tbl AS Table1
CROSS APPLY Xml.nodes('*:EventD/*:NewValue[text()="Running"]') AS Only(Running)