Another SQL XML tidbit

So after all my blodd sweat and tears went into getting SQL to write well formed XML from our multiple tabled resume database… A minor glitch.



We had initially encoded everything withing a CDATA directive when SQL Server builds the XML. Well then in getting it to work with the neat XML data type, we made that not work. That’s the short and sweet of it.



According to the experts on one of M$, usenet newsgroups, yes in fact those are still around. When using FOR XML EXPLICIT in your XML construction, if you add ",TYPE" to that it negates the CDATA directives. TYPE puts the output into the XML datatype, which it seems doesn’t care for the CDATA directive.



I had to go through all the procs and essentially take all my datatypes from XML, to nvarchar(max) so that CDATA would work, and then remove the TYPE directive at the end of my statement.



@xmldata xml output —>  @xmldata nvarchar(max) output

and

FOR XML EXPLICIT, TYPE —>  FOR XML EXPLICIT



The only downside to this as far as I can tell so far, is we’re not taking advantage of the native (now) XML datatype in SQL Server 2k5. Oh well.