An Easier Way to do Complex FOR XML EXPLICIT

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:

PATH T-SQL Xml OutputWith T-SQL as simple as this:

Xml PATH SQL T-SQLMichael Rys has a couple of good blog posts on the subject here and here – well worth a read.

Advertisements

4 thoughts on “An Easier Way to do Complex FOR XML EXPLICIT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s