How are your XPath skills? I have been finding a need to learn XPath lately for mining xml data in a database, and for optimizing the App.Config for my Dot Net applications using Slow Cheetah (see previous editorial on Slow Cheetah).
I have to say that the documentation on a lot of XPath I have found on the internet has been painful. They would provide the query and the results of applying the query against the source, and show you the results the XPath would locate. The problem was that every example from Microsoft did not contain the original XML that was being searched or transformed. So, you had to guess what the contents looked like.
I probably wasted 4 hours figuring out syntax for Slow Cheetah because they use a little bit different transform method than the W3C and it is not as well documented from what I can find. Now that I have gotten it down it is working rather nicely.
Then, I get a lot of input from external vendors with their parameters sent in XML. They had a version attribute in the root node. It was extremely difficult to figure out the syntax to capture that attribute in an SQL query (the xml is stored in a table in our database) using XPath, and the errors for incorrect syntax were less than helpful.
I am not the most talented coder around. However, I found this quite frustrating. I then found an entire room of other developers much smarter than me who all said, “I just stay away from XPath. It’s a time synch, and hard to learn.”
So, I guess it is my turn to give back to the community at large. With a few painful hours, I figured it out. I’m still no expert, but I seem to be able to extract the information I need, and even to be able to filter on the contents.
So, are you using XML in your SQL databases? If so, how important is learning the XPath syntax to you when it comes to mining the XML contents? If it’s not important, then why are we using an XML datatype to store data?
Share your thoughts here or drop me an Email at btaylor@sswug.org.
Reader Feedback – Concatenating Strings:
Richard Writes:
Don’t recall how we were supposed to email you, but I stumbled across this method online a while back and it works well for us:
SELECT STUFF((
SELECT DISTINCT ‘, ‘ + City.Name AS [text()]
FROM City
WHERE City.Name LIKE ‘ALAM%’
FOR XML PATH(”)),1,2,”)
I never bothered determining why it works. I’m just happy it does.
Cheers,
Ben