The easiest way is just to tack a FOR XML AUTO onto the end of a SELECT query. But that isn't always what you want. Sometimes you want a fragment, a single data value wrapped up into a tag.
The trouble is, that data might contain a " or a & or worse, a < or > character. If you try SELECT '<Column1>' + Column1 + '</Column1>' sooner or later you'll generate malformed XML, that won't parse. This is the great thing about XML AUTO, it does the XML version of UrlEncode on all your data. But it generates a complete document. You could shred the output, but there's an easier way.
A blog called System.Reflection.Emit has published a great tutorial on how to do this. It involves writing a very simple SQL CLR function ( at least, I'd recommend deploying it as a scalar function) that makes short work of obeying the rules of XML, and producing fragments.
Code:
namespace XmlUtil {
private static XmlDocument _staticDoc = null;
private static StringWriter _staticStringWriter = null;
private static XmlWriter _staticXmlWriter = null;
/// <summary>Converts Unicode text into ASCII-compliant XML encoded text</summary>
public static string EncodeText(string str)
{
if (str == null) return "";
if (_staticDoc == null)
{
_staticDoc = new System.Xml.XmlDocument();
_staticDoc.LoadXml("<text></text>");
_staticStringWriter = new StringWriter();
XmlWriterSettings settings = new XmlWriterSettings();
settings.ConformanceLevel = ConformanceLevel.Fragment;
_staticXmlWriter = XmlTextWriter.Create(_staticStringWriter, settings);
}
lock (_staticDoc)
{
_staticDoc.LastChild.InnerText = str;
str = _staticDoc.LastChild.InnerXml;
}
// ASCII enforcement
StringBuilder sb = new StringBuilder();
char[] chars = str.ToCharArray();
for (int i = 0; i < chars.Length; i++)
{
char c = chars[i];
if ((int)c > 127) // goes beyond ASCII charset
{
lock (_staticStringWriter)
{
lock (_staticXmlWriter)
{
_staticXmlWriter.WriteCharEntity(c);
_staticXmlWriter.Flush();
StringBuilder _sb = _staticStringWriter.GetStringBuilder();
sb.Append(_sb.ToString());
_sb.Length = 0;
}
}
}
else sb.Append(c);
}
return sb.ToString();
}
}
|
