SQL Server XML output with CDATA into xml variable

Question

Following this question, I need to put the select result into a xml variable. How to do this avoiding the error message "The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table or common table expression or view and apply FOR XML or FOR JSON on top of it.", please?

Here the test code:

declare @agent table
(    
    AgentID int,
    Fname varchar(5),
    SSN varchar(11)
)

insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!cdata'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL, 
    AgentID,
    Fname,
    SSN
FROM @agent
FOR XML EXPLICIT

And here an example of what I want to do:

Declare @xml xml
...
set @xml= (SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!cdata'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL, 
    AgentID,
    Fname,
    SSN
FROM @agent
FOR XML EXPLICIT)

score 1 · Answer 1 · answered Nov 16 '17 at 19:01

This is tricky...

You can move the UNION ALL part to a CTE like here. The problem is not the FOR XML but rahter the UNION (be carefull, it might be necessary to add an ORDER BY clause):

DECLARE @xml XML;

WITH UnionAllCte AS
(
    SELECT
        1 AS Tag,
        NULL AS Parent,
        NULL AS 'Agents!1!',
        NULL AS 'Agent!2!AgentID',
        NULL AS 'Agent!2!Fname!Element',
        NULL AS 'Agent!2!SSN!cdata'
    UNION ALL
    SELECT
        2 AS Tag,
        1 AS Parent,
        NULL, 
        AgentID,
        Fname,
        SSN
    FROM @agent
)
SELECT @xml=
(
    SELECT * FROM UnionAllCte
    FOR XML EXPLICIT
)

SELECT @xml;

And you should be aware, that CDATA as well as FOR XML EXPLICIT are outdated. Rather use FOR XML PATH() and for reading the appropriate methods the XML data type provides.

You might read this and read the following links too!

SQL Server XML output with CDATA into xml variable

1 Answers1