How do I build XQuerys to include/exclude rows based on the presence of certain tags and attributes?

Question

I have an auditing/logging system that uses raw XML to represent actions taken out by an application. I'd like to improve on this system greatly by using an XML column in a table in the application's SQL Server database.

Each row in the table would contain one log entry and each entry should contain one or more tags that are used to describe the action in a semantic fashion that allows me to search in ways that match the auditing needs of the application, example:

<updateInvoice id="5" userId="7" /><fieldUpdate name="InvoiceDate" /><invoice /><update />

<deleteInvoice id="5" userId="6" /><invoice /><delete />

My intention is to return rowsets from this table by specifying combinations of tags and attributes to include or exclude rows by (e.g. "Include all rows with the tag invoice but exclude rows with the attribute userId='7'", or "Include all rows with the tag invoice but exclude rows with the tag delete)

I wish to do so programatically by using combinations of a simple filter structure to represent combinations of tags and attributes that I want to cause rows to be either included or excluded.

The structure I use looks like this:

enum FilterInclusion { Include, Exclude };

public struct Filter
{
    FilterInclusion Inclusion;
    string TagName;
    string? AttributeName;
    object? AttributeValue;
}

My goal is to accept a set of these and generate a query that returns any rows that match any single inclusion filter, without matching any single exclusion filter.

Should I and can I encode this boolean logic into the resulting XPath itself, or am I looking at having multiple SELECT statements in my outputted queries? I'm new to XQuery and any help is appreciated. Thanks!

Answer 1

I'm not sure if that's what you're looking for, but to filter nodes in XML methods you use the brackets [ and ]. For instance to select the elements foo but filter to only those that have the attribute bar you'd use an XPath like /foo[@bar]. If you want those that have the attribute @bar with value 5 you use /foo[@bar=5]. If you want to select the elements foo that have a child element bar you use /foo[bar].

declare @t table (x xml);
insert into @t (x) values 
(N'<foo bar="abc"/>');
insert into @t (x) values 
(N'<foo bar="5"/>');
insert into @t (x) values 
(N'<foo id="1"><bar id="2"/></foo>');
select * from @t;

select c.value(N'@bar', N'varchar(max)') 
from @t cross apply x.nodes(N'/foo[@bar]') t(c)

select c.value(N'@bar', N'varchar(max)') 
from @t cross apply x.nodes(N'/foo[@bar=5]') t(c)
select c.value(N'@id', N'int') 
from @t cross apply x.nodes(N'/foo[bar]') t(c)

I tried to show the examples on the XML snippets in your post, but there those are too structureless to make useful examples.