Cursors and the evil that men do...

I had almost invariably been told by DBAs of my acquaintance that cursors are evil, a monstrosity born of desperation by the clever people who design relational database management systems to allow mere mortal users to work outside of the pure set based logic that rules within the realm of SQL.I have also been generally advised that recursion within code is often terribly clever but apart from the most basic of cases it's hard to write and hard to maintain.

 

I have recently been working on a piece of software that has to flatten out some XML and load it into a relational database.  The details of the database or the XML are irrelevant. What is clear is that the XML was concocted (I hesitate to say designed) in such a manner that when flattened out the only way that I can see to spring it back to life is either through recursion and/or a cursor.

 

MS SQL Server has a feature CTE that allows some of this to work but for this type of work I prefer to try and stick to SQL standard features rather than vendor implementation specifics so I have used a nested cursor pattern.

 

It works and with a few tweaks, index changes and hints it works beautifully.  I still feel a little unclean though.

Not this type of cursor, this one rocks...