I have table Web.Log with column Properties which contains following content
<properties>
...
<property key="ActivityId" />
<property key="UserName">John Doe</property>
</properties>
How can I filter Web.Log table only on rows, which contains John Doe userName property ?
Now I use
select * from Web.Log
where cast(Properties as nvarchar(max))
like '%<property key="UserName">John Doe</property>%'
which feels like workaround :-)
It should be faster to use the native XML method .exist().
First create a mockup scenario to simulate your issue:
DECLARE @tbl TABLE(ID INT IDENTITY, SomeText VARCHAR(100),Properties XML);
INSERT INTO @tbl VALUES('John Doe exists'
,'<properties>
<property key="ActivityId" />
<property key="UserName">John Doe</property>
</properties>')
,('John Doe doesn''t exist'
,'<properties>
<property key="ActivityId" />
<property key="UserName">Someone else</property>
</properties>')
,('John Doe exists'
,'<properties>
<property key="ActivityId" />
<property key="UserName">John Doe</property>
</properties>');
--We can use variables to filter generically
DECLARE @SearchFor VARCHAR(100)='UserName';
DECLARE @FindThis VARCHAR(100)='John Doe';
--This is the query:
SELECT *
FROM @tbl t
WHERE t.Properties.exist('/properties/property[@key=sql:variable("@SearchFor") and text()[1]=sql:variable("@FindThis")]')=1;
The idea in short:
.exist() directly in the WHERE clause.=1 can be set to =0 in order to return the negative set<property> with the given key and value.
Use XQUERY and an EXISTS:
SELECT {Columns}
FROM Web.Log L
WHERE EXISTS (SELECT 1
FROM L.[Properties].nodes('/properties/property')p(p)
WHERE p.p.value('(text())[1]','nvarchar(50)') = 'John Doe')
XML.exist(). Using XML.nodes() together with a WHERE will need to read the full set, just to check for the existance of one key-value pair...