Harish, you are a very hard working and great contributor to this forum. Keep it up! This forum is a great resource to help you refine your skills with best practices as we have very talented people giving free advice. Take advantage of it! I know I am always learning and have been helped by many here.
Here is something you might want to read about what @JeffModen is trying to convey. And notice the detailed testing and bench marking in that post.
@yosiasz beat me to it because this forum doesn't allow anyone to make more than 3 consecutive posts. I had to wait for someone else to post before I could post the following. It contains a partial duplication of what @yosiasz just posted.
For those interested, the following article explains and compares several splitting methods. It clearly demonstrates that the XML method of splitting is one of the slowest there is.
Yes... I understand that there are a great many articles out there that supposedly prove that the XML method is the fastest but the "Devil's in the Data" for those. They use "grooved" data (highly repetitive data) that has a very low cardinality that doesn't normally appear in data, which XML favors. If you put such a thing into production where "real" data occurs, performance will seriously be made to suffer.
The following article demonstrates why it's so bad to use incremental rCTEs even for small stuff.
for example I love xml. You could say I cut my teeth with software development that involved crunching xml data from a site that saved user entry into xml. When I got involved in more SQL Dev work, I brought my love for xml with me and enjoyed implementing it for many of my work. But though xml is nice, it did not scale too good when being used in sql.
Also, for those interested, as of SQL Server 2012, there is an optimization for the DelimitedSplit8K function that was designed and tested by a good friend of mine. It is quite literally twice as fast because it uses the LEAD function to replace the rather expensive CHARINDEX function. You can find the post where my friend ( Eirikur Eiriksson )explains it at the following URL in Part 1 of his fine article...
And, just to emphasize, if all that is desired for this problem is to find whatever is between the last two slashes, @ScottPletcher 's solution is probably the best way to go. Splitting the entire string just to find the last element is comparatively much more expensive.
You should write an article on the subject, Tito. There are a whole lot of newbies-to-XML-in-SQL that could use a good article on XML and how to manage it and use it in T-SQL. I include myself in that "newbie" category because I've tried to avoid it like the plague but that avoidance has cost me. Speaking for a lot of other folks, I could use a really good tutorial on how to figure things out in T-SQL for XML.
You've identified one of your many interests as "Leonardo da Vinci"... have you ever built a "da Vinci Bridge" out of a handfull of 2 x 4's? It's an interesting and very quick project for both kids and adults alike. Just think what the man could have accomplished if he were alive today.
Getting back to this and to answer the above... if you follow someone else, you'll never be as good as they are. To reverse an old saying to make it much more true, "Great minds do NOT all think alike".
I've also found that learning is like magnetic materials...
Soft Iron can be nearly instantly be magnetized but it looses it's magnetism very quickly (think electro-magnet).
Nickle is more difficult to magnetize but will stay magnetized much longer.
Cobalt is even more difficult to magnetize but also stays magnetized for a whole lot longer than even Nickle.
Certain ceramics are nearly impossible to magnetize except when nearly in a molten state and must be cooled in the presence of a very high magnetic field for them to retain the magnetism. Once cooled, however, they're a bit like lode-stone and will retain their magnetism virtually forever.
Quick learning might actually teach you the wrong things. For example, a quick study might clearly identify what all others think of as "Best Practices" for index maintenance. Going for the ceramic method with lots of experiments will tell you how wrong those people are, why they're wrong, and what can actually be done correctly. In the process, always remember that "Great minds do NOT thing alike".