Nick Heppleston’s BizTalk Blog

I’ve got to confess that I’m not all that good with the FOR XML EXPLICIT syntax to generate Xml straight out of SQL Server – although there are a few good resources on the technology, I don’t spend enough time writing it and just get hopelessly muddled when I have to write anything remotely complex.

So, after posting a plea for help to the SQL Server Xml MSDN Forum I was pleasantly surprised to learn about the new PATH Mode for generating Xml that is available in SQL Server 2005. To quote the MSDN website:

“…the PATH mode provides a simpler way to mix elements and attributes. PATH mode is also a simpler way to introduce additional nesting for representing complex properties. You can use FOR XML EXPLICIT mode queries to construct such XML from a rowset, but the PATH mode provides a simpler alternative to the potentially cumbersome EXPLICIT mode queries. PATH mode, together with the ability to write nested FOR XML queries and the TYPE directive to return xml type instances, allows you to write queries with less complexity.”

So, using PATH mode, you can generate Xml along the lines of:

With T-SQL as simple as this:

Michael Rys has a couple of good blog posts on the subject here and here – well worth a read.