For XML Explicit gotchas

Free asked me something today which gave me the idea it put my answer in writing so I don’t forget it.

He was working on one of my procs and it was returning an error, that tag ID 2 was not open for use and had to be opened first.

I saw that enough to remember the culprit, or at least the common culprit for me. For XML Explicit needs the query output to be very specific in its ordering. Very very specific. If a single column is out of whack you will likely have troubles.

What I found was helpful (from a blog I didn’t take note of I’m sure) is to remove

FOR XML EXPLICIT

which will make your Stored Proc of query return a standard recordset. This way you can see the output and see your result rows. Each row needs to be output in numeric order basically. If row 1 has tag 1 and row 2 has tag 3, row 3 can’t have tag 2. Bad Juju ensues.

This little trick saved me so much greif.