How to generate CDATA and NAMESPACE Declarations using FOR XML EXPLICIT

I was asked this question in one of the SQL Server forums recently. The OP wanted to generate an XML document with CDATA sections and NAMESPACE declarations.

SQL Server 2005 introduced WITH XMLNAMESPACES() which is very handy to generate namespace declarations in the output XML document generated by FOR XML. FOR XML EXPLICIT can be used to generate CDATA sections in the XML output. However, what is interesting is that you cannot use WITH XMLNAMESPACES and FOR XML EXPLICIT together.

Here is a FOR XML EXPLICIT query that generate CDATA sections as well as XML NAMESPACE declarations in the output XML document.

SELECT
	1 AS Tag,
	NULL AS Parent,
	'Jacob' AS 'Person!1!Name!element',
	'Hello world' AS 'Person!1!Greeting!CDATA',
	'http://beyondrelational.com/xml.aspx' 
		AS 'Person!1!xmlns:x'
FOR XML EXPLICIT

Here is the output

<Person xmlns:x="http://beyondrelational.com/xml.aspx">
  <Name>Jacob</Name>
  <Greeting><![CDATA[Hello world]]></Greeting>
</Person>

Jacob Sebastian 1 · 100% · 16868