CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Pertinent Observer
Pertinent Observer

Reputation: 311

SET XML EXPLICIT QUERY TO VARIABLE

I need to generate the following XML string in SQL and assign it to a variable.

<List>
   <V Value="TESt">
       <Target a="10" b="11"/>
   </V>
   <T Value="TESt1">
       <Target a="100" b="101"/>
   </V>
</List>

So, I wrote the following SQL code:

;WITH XML_CTE AS
(
SELECT 
    1 AS TAG,
    NULL AS Parent,
    UT.title AS [v!1!Value],
    null AS [Target!2!a],
    null AS [Target!2!b]
FROM 
(
    select  
        10 AS A,
        11 AS B,
        'TESt' as title
    union
        select  
        100 AS a,
        101 AS b,
        'TESt1' as title
)UT
UNION ALL
SELECT
    2,
    1,
    UT.title AS Value,
    ut.a,
    ut.b
FROM
(
    select  
        10 AS a,
        11 AS b,
        'TESt' as title
    union
        select  
        100 AS a,
        101 AS b,
        'TESt1' as title
)ut
)
SELECT * FROM XML_CTE 
ORDER BY 
    [V!1!Value], 
[Target!2!a]
FOR XML EXPLICIT, ROOT('List')

But how do I assign it to a xml(or nvarchar) variable? Because of the "UNION ALL", I had to wrap it in CTE, but having it in a CTE, I am unable to assign it to a variable.

Upvotes: 4

Views: 4268

Answers (2)

marc_s
marc_s

Reputation: 754458

Assuming you have an initial set of data something like:

Col1     A     B
------------------
TESt     10    11
TESt1   100   101

then I would recommend using FOR XML PATH,ROOT rather than the old, legacy EXPLICIT mode (which is really complicated and convoluted)

DECLARE @result NVARCHAR(MAX)

;WITH XML_CTE(Col1, A, B) AS
(
  SELECT 
     'TESt', 10, 11
  UNION
  SELECT    
    'TESt1', 100, 101
)
SELECT @Result =
   (SELECT
       Col1 AS '@Value',
       A AS 'Target/@a',
       B AS 'Target/@b'
    FROM XML_CTE 
    FOR XML PATH('V'), ROOT('List')
   )

Gives me an output of:

<List>
  <V Value="TESt">
    <Target a="10" b="11" />
  </V>
  <V Value="TESt1">
    <Target a="100" b="101" />
  </V>
</List>

as you asked for.

Upvotes: 3

bobs
bobs

Reputation: 22184

You could alter the SELECT * FROM XML_CTE... statement to the following

SELECT @MyVariable = 
    (SELECT * FROM XML_CTE 
    ORDER BY 
        [V!1!Value], 
    [Target!2!a]
    FOR XML EXPLICIT, ROOT('List')
    )

Upvotes: 3

Related Questions