This is the follow-up post on FOR XML EXPLICIT that I promosed in the previous post. We saw some of the basics of FOR XML EXPLICIT in the previous post. Let us see a few more interesting usages of FOR XML EXPLICIT in this post.

All the examples we saw in the previous posts demonstrated how to generate XML documents from the values stored in string/numeric/date columns of one or more tables. The next example shows how to generate an XML document from XML columns.

DECLARE @t TABLE (data VARCHAR(100))
INSERT INTO @t (data) SELECT '<Info about="XML"/>'

SELECT
    1 AS Tag,
    NULL AS Parent,
    data AS 'MyData!1!!ELEMENT'
FROM @t
FOR XML EXPLICIT
/*
<MyData>&lt;Info about="XML"/&gt;</MyData>
*/

In the above example, an XML document is created using the FOR XML EXPLICIT techniques we learned in the previous posts. However, the XML output we generated is not what we wanted. SQL Server has encoded XML tags in the output which is not acceptable in our case.

By specifying the XML attribute on the column name, you can instruct SQL Server to treat the value in the column as an XML fragment and this will fix the problem we saw earlier. Here is the new version of the query.

DECLARE @t TABLE (data VARCHAR(100))
INSERT INTO @t (data) SELECT '<Info about="XML"/>'
SELECT
    1 AS Tag,
    NULL AS Parent,
    data AS 'MyData!1!!XML'
FROM @t
FOR XML EXPLICIT
/*
XML directive preserves XML tags
<MyData>
  <Info about="XML" />
</MyData>
*/

Let us see another tricky XML shaping requirement. Assume that you have the following data in the table.

DECLARE @t TABLE (name VARCHAR(20), data VARCHAR(100))
INSERT INTO @t (name, data) 
SELECT 'Jacob', '<Info about="XML"/>'

Here is the XML document that you need to generate from the above data.

/*
<MyData about="XML">Jacob</MyData>
*/

This can be achieved by using the XMLTEXT attribute on the column name of your FOR XML EXPLICIT query. Here is the version of the query that does this.

DECLARE @t TABLE (name VARCHAR(20), data VARCHAR(100))
INSERT INTO @t (name, data) 
SELECT 'Jacob', '<Info about="XML"/>'

SELECT
    1 AS Tag,
    NULL AS Parent,
    data AS 'MyData!1!!XMLTEXT',
    name AS 'MyData!1!'
FROM @t 
FOR XML EXPLICIT

/*
<MyData about="XML">Jacob</MyData>
*/

The following example demonstrates how to generate CDATA elements using FOR XML EXPLICIT.

DECLARE @t TABLE (name VARCHAR(20), data VARCHAR(100))
INSERT INTO @t (name, data) 
SELECT 'Jacob', '<Info about="XML"/>'

SELECT
    1 AS Tag,
    NULL AS Parent,
    data AS 'MyData!1!!CDATA'
FROM @t
FOR XML EXPLICIT
/*
<MyData><![CDATA[<Info about="XML"/>]]></MyData>
*/

I think I will still need one more post to explain how to generate XML documents with namespace declarations. I will cover it in the next post.