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.