XPATH for case insensitive search (translate function)

Guyz!

You know XML is case sensitive. But there are different situations when we have to deal with case insensitive search. Since XPATH is also for XML it has to be case sensitive. But can I search something in an XML document. I want my search to be case insensitive. This is because I know that my search text or pattern may be in a combination of different cases. But is there a workaround possible so that I could search an XML document for a pattern using case insensitive search.


You would be glad to know there is a way around. This is achieved using translate function in XPATH. This function is actually used to replace any combination of characters with some other combination of characters. The character in the pattern is replaced by character on the same position in the replace expression.

The syntax is as follows:

translate( text, pattern, replace expression)

Lets do an exercise. For example we have an XML element ‘MyDate’ which has information about data in the format: MM-DD-YYYY. We want to change hyphens (-) in the date to forward slashes (/). The translate function can be written like this:

translate(MyDate, ‘-’, ‘/’)

Now come to our case, what if we convert the case of our text to lower. Now we change the case of our pattern to lower. Now if we would do a search, we can do it easily. Lets write our translate function for this:

translate( MyText, ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’, ‘abcdefghijklmnopqrstuvwxyz’)

Now the first character in the pattern is ‘A’. If this is found in MyText, it would be replaced with ‘a’. The second character is ‘B”, when found, it would be replaced with second character in MyText. This would be same for every character in the pattern string.

Now you must be thinking if the length of pattern is greater than replace expression. In that case all those positions in the pattern expression which have no character in the corresponding positions in the replace expression, are just removed from MyText. So if we want to remove all hypens from our date, we would use translate function like this:

Translate(MyDate, ‘-’, ‘’)

And our XPATH expression might be like this:

//MyXMLElement[translate(@MyDate, ‘-’, ‘’) = ‘12022009’]

If you want to test your XPATH expressions, then there is an excellent online free tool. This link is as follows:

XPATH Online tool

C# as scripting language in Integration Services

There is good feature of C# developers using integration services. Now you can use C# as scripting language of Script Task.
You can write script by taking properties of script task:


You can see that you have option to select scripting language. Currently there are two options. These languages include C# and VB.net