Use xQuery to extract attribute values that also exist in SQL variable

Question

XML structure:

<ns0:message xmlns:ns0='xxx:testing'>
  <ns0:field name='AAA'>...</ns0:field>
  <ns0:field name='BBB'>...</ns0:field>
  <ns0:field name='VVV'>...</ns0:field>
  <ns0:field name='CAR'>...</ns0:field>
</ns0:message>

I have SQL that will extract the values of the attributes titled name:

SELECT
  ( (SELECT ',' + CHAR(13) + P.N.value('@name', 'varchar(max)')
       FROM myTable.message_xml.nodes('declare namespace ns0="xxx:testing";
                                       ns0:message/ns0:field[ @name = "AAA" or
                                                              @name = "BBB"]') P(N)
        FOR XML PATH(''), type).value('substring(text()[1], 3)', 'varchar(max)')) as ATTRIBUTE_VALUES

This returns a column that looks like:

ATTRIBUTE_VALUES
---------------
AAA,
BBB

My problem is that the list of potential attribute values is quite large.
Instead of repeating @name = "AAA" in my query for every attribute value I want to check for, I was hoping I could declare it as a variable like:

DECLARE @ATTRIBUTES VarChar(Max)
SET @ATTRIBUTES = '(AAA,BBB,CAR,XYZ)'

And then just stick the variable in the sql like:

[@name = sql:variable("@ATTRIBUTES")]

but this is not working for any combination of parens,commas,etc I use to build the variable.

Answer 1

XML in SQL Server is good at shredding and getting values so for performance it is probably almost best to focus on extracting the data you want and then do the logic in SQL instead.

Put the values you are looking for in a table of some kind and filter your extracted values against that table instead. If your input is a string you could use a string split function of your choice to get them into a table.

declare @T table(X xml);
insert into @T(X) values('<ns0:message xmlns:ns0=''xxx:testing''>
  <ns0:field name=''AAA''>...</ns0:field>
  <ns0:field name=''BBB''>...</ns0:field>
  <ns0:field name=''VVV''>...</ns0:field>
  <ns0:field name=''CAR''>...</ns0:field>
</ns0:message>');

declare @V table(name varchar(10));
insert into @V(name) values('AAA'),('BBB'),('CAR'),('XYZ');

with xmlnamespaces(default 'xxx:testing')
select T.name
from (
     select F.X.value('@name', 'varchar(10)') as name
     from @T as T
       cross apply T.X.nodes('/message/field') as F(X)
     ) as T
where exists (
             select *
             from @V as V
             where V.name = T.name
             );

If you are really looking for a XQuery solution you could use contains

declare @T table(X xml);
insert into @T(X) values('<ns0:message xmlns:ns0=''xxx:testing''>
  <ns0:field name=''AAA''>...</ns0:field>
  <ns0:field name=''BBB''>...</ns0:field>
  <ns0:field name=''VVV''>...</ns0:field>
  <ns0:field name=''CAR''>...</ns0:field>
</ns0:message>');

declare @S varchar(100) = ',AAA,BBB,CAR,XYZ,';

with xmlnamespaces(default 'xxx:testing')
select T.X.query('data(/message/field/@name[contains(sql:variable("@S"), concat(",", ., ","))])')
from @T as T;