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

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

You can use contains function. Declare a variable like so:

DECLARE @ATTRIBUTES VarChar(Max) = '|AAA|BBB|CAR|XYZ|';

And in you query instead of @name = "AAA" use:

contains(sql:variable("@ATTRIBUTES"), concat("|", @name, "|"))

Advertisement

Answer