Distinct Value Extractor using Xpath to filter

[Former Member]

I am using a distinct value extractor and XPath to filter the results of a SQL query. basically taking the dataset returned from SQL and filtering based on a ID.

the xpath looks similar to this

SQLQuery.Results{/Rowsets/Rowset/Row[(IDFIeld = '#Transaction.pID#')]/DescriptionField}

What I want to do, is filter based on a comma delimited list using an "In" statement simlar to SQL...example Select * from table where ID in (1,2,3)

Is this possible in Xpath?

BTW

I know I could do this at the DB/SQL level, but I am filtering the SQL dataset multiple times throughout the BL and am using Xpath to minimize the amount of data sent between XMII and the DB

[Former Member · Accepted solution]

Hi, Jeff.

You can have composite conditions in XPath, as in:

SQLQuery.Results{/Rowsets/Rowset/Row[IDFIeld = '1' or IDFIeld = '2' or IDFIeld = '3']/DescriptionField}

What I would do is create a Local String property called "MatchExpression", and use the following for your Distinct selection expression:

SQLQuery.Results{/Rowsets/Rowset/Row[#Local.MatchExpression#]/DescriptionField}

Then, I would add logic to build the MatchExpression based on the list of items.

The way I would do this is:

0) Assign empty string to Local.MatchExpression

1) Use StringListToXmlConverter on your comma separated list

2) Repeat for each item in the output

3) If not first item (Repeater.CurrentItem > 1), append " or " to Local.MatchExpression by assigning Local.MatchExpression & " or " to Local.MatchExpression

4) Append the delimited value (include the single quotes)

This way, it is completely flexible and dynamic - you just pass in a comma-separated list and it all magically works!

- Rick

[Former Member · Accepted solution]

I'd also add a Transaction Input named "MatchColumnName", to make it completely flexible. In this way, you could build a reusable transaction that could be leveraged from within other transactions.

[Former Member · Accepted solution]

Thanks to both of you for your help. I was able to add your logic to my app and it works great... Using a seperate transcation to modularize the application, was a great idea for reuse. Next thing I know, your going to be showing me Inheritance and Polymorphism in Business logic.

thanks,

Jeff

[Former Member]

An expression like [(IDFIeld = '#Transaction.pID#') OR IDFled='<value2>' OR IDFled='<value3>'.......<valueN>] should work for you.

Only thing you need to figure out prior to using the DistinctValue action is reading the comma separated string where StringListToXML action would help you.