CODEWITHSUNDEEP
xmlt-sqlsql-server-2014
Dave Johnson
Dave Johnson

Reputation: 825

XML EXPLICIT Into Variable

I am trying to get an XML field into an SQL variable (and then ultimately into a table column), but am getting an error.

If I run

Declare @tvTable Table (
    id int IDENTITY(1,1)
    ,someThing varchar(100)
    ,otherThing varchar(100)
    ,thisThing varchar(100)
);

Insert  @tvTable
Values  ('stuff', 'blah', 'foo')
        ,('thing', 'data', 'bob');

Select  [Tag]                       = 1
        ,[PARENT]                   = NULL
        ,[things!1!thingId]         = NULL
        ,[thing!2!thingId!element]  = NULL
        ,[thing!2!thingOne!element] = NULL
        ,[thing!2!thingTwo!cdata]   = NULL
        ,[thing!2!thingThree!cdata] = NULL
UNION ALL
Select  2
        ,1
        ,1
        ,thingId    = id
        ,thingOne   = someThing
        ,thingTwo   = otherThing
        ,thingThree = thisThing
From    @tvTable
FOR XML EXPLICIT;

Then I receive a proper return

<things>
  <thing>
    <thingId>1</thingId>
    <thingOne>stuff</thingOne>
    <thingTwo><![CDATA[blah]]></thingTwo>
    <thingThree><![CDATA[foo]]></thingThree>
  </thing>
  <thing>
    <thingId>2</thingId>
    <thingOne>thing</thingOne>
    <thingTwo><![CDATA[data]]></thingTwo>
    <thingThree><![CDATA[bob]]></thingThree>
  </thing>
</things>

However, as soon as I try to dump that into a variable, I get an error:

The FOR XML clause is 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 syntax and apply FOR XML on top 
of it.

I need to use XML EXPLICIT because I need to have certain fields wrapped in proper CDATA tags (initial attempt just threw those in as strings, and the vendor rejected the file).

How can I modify this query such that I can store the return in a variable?

Upvotes: 0

Views: 595

Answers (1)

S3S
S3S

Reputation: 25112

Handle the XML on the variable set not on the query itself. 

Declare @tvTable Table (
    id int IDENTITY(1,1)
    ,someThing varchar(100)
    ,otherThing varchar(100)
    ,thisThing varchar(100)
);

Insert  @tvTable
Values  ('stuff', 'blah', 'foo')
        ,('thing', 'data', 'bob');

declare @someVar nvarchar(max)

;with cte as(
Select  [Tag]                       = 1
        ,[PARENT]                   = NULL
        ,[things!1!thingId]         = NULL
        ,[thing!2!thingId!element]  = NULL
        ,[thing!2!thingOne!element] = NULL
        ,[thing!2!thingTwo!cdata]   = NULL
        ,[thing!2!thingThree!cdata] = NULL
UNION ALL
Select  2
        ,1
        ,1
        ,thingId    = id
        ,thingOne   = someThing
        ,thingTwo   = otherThing
        ,thingThree = thisThing
From    @tvTable)


select  @someVar = (select * from cte FOR XML EXPLICIT)

select @someVar

Upvotes: 2

Related Questions