Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this tSQL code which works OK:

a.b.value('./PropertyValue', 'varchar(max)') asd
  FROM [dnn].[dbo].[EventLog2] c

cross apply sss.nodes('/LogProperties/LogProperty[PropertyName=sql:variable("@x") and PropertyValue=sql:variable("@y")]') as a(b)

However, what I want to be able to do is pass in a dynamic list of many pairs of values which are OR'd between each one, i.e.

a.b.value('./PropertyValue', 'varchar(max)') asd
  FROM [dnn].[dbo].[EventLog2] c

cross apply sss.nodes(
'/LogProperties/LogProperty[PropertyName=sql:variable("@x") and PropertyValue=sql:variable("@y")
PropertyName=sql:variable("@a") and PropertyValue=sql:variable("@b")
) as a(b)

Is there a way of doing this?

share|improve this question
To make sure I understand, there could also be @c and @d etc? – wtjones Sep 4 '12 at 22:15

Any parameter that needs a variable list or array is probably a good candidate for a user-defined table type. I would create the type as:

CREATE TYPE [PropertyVariableTableType] AS TABLE (
    PropertyName nvarchar(255),
    PropertyValue nvarchar(255) )

Table types can be used as parameters to stored procedures just as any other type. You can then either join on the user-defined table or iterate the rows to dynamically build the query.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.