rmechaber (5/3/2012)
peter-757102 (5/3/2012)
They are unable to model their data properly and often do not handle escaping well, causing errors in individual elements. But XML escaping is extremely simple and requires only a few find/replace actions you can do in practically every programming environment. It is truly a lack of basic knowledge that is the culprit here. I developed a toolset to work around the encoding issue by dropping those specific objects of interest that have these errors and keep processing the rest of the document.Indeed, as an XML novice I found escaping an extremely annoying and frustrating task. (This was to produce XML to be read by PHP for displaying on a website.) In addition, I couldn't find an easy way to write out XML from SQL to a file with an included XML header, so I have used a rather clunky work-around.
Would you have any good links/references about how to handle this?
Thanks,
Rich
There are several ways, but so far I only had to generate XML from the application layer and not SQL. Once you know how and when to escape data, the problem is reduced to nothing more then concatenating strings in a natively efficient manner when using this method. For SQL specifically there are other ways as well, some likely more efficient (and complex), but I got no experience with them.
You could start with SELECT and the FOR XML clause for simple set representations in XML, but the output does not necessarily match what you need to generate (XML works case sensitive for example).
Documentation for the FOR XML clause: http://msdn.microsoft.com/en-us/library/ms190922(v=sql.100).aspx
As for exporting data to a file, that is something I found always lacking from T-SQL. Importing is easy and has fairly good support, exporting not so...it would be my number one wish for any new release! I hate DTS and any such overly complex replacement and just expect to be able to specify an output file in my SQL clause and be done with it (for the sake of both sanity and efficiency).
I assume you know the 5 characters that you need to replace? (this forum did not allow me to post them as it generated an error)