T-SQL: XML from SQL, Managing Escaped Characters

T-SQL contains within it a module which lets you generate XML data directly from SQL. No concatenating strings or building the XML layout by hand required!

When you jump into using FOR XML, and exporting the data to a file, eventually you’re going to encounter a situation where a data element has one of the reserved (or escaped) characters in it, like an “&”.

When that happens, you get data output like:

Morven & Deacongard

Or the output of this query:

Which is not necessarily what you want to display to a customer. You could address this by doing multiple REPLACE() calls, but that gets unwieldy as you find more and more reserved characters you need to switch around.

However! There is an easier alternative, provided by a function provided the FOR XML module itself, VALUE(), which we use like this:

This dos the same processing, gets the delimited list in XML, and then converts the list into a regular VARCHAR. When that happens, the escaped characters, like “&”, are swapped back to their regular equivalents.

Easy peasy!

And it handles all of the escaped characters, so you don’t have a huge gob of REPLACE()’s to manage.

Extra Credit

You realize this lets you execute sub-queries or OUTER APPLY clauses to produce delimited lists of codes inside a single SQL result set field, right? Yes it does. Try that out.

And…

Want an on-line training class in how to generate XML from SQL? Let us know, we can deliver that for you.

As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!