SET XML EXPLICIT QUERY TO VARIABLE

Question

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.

bobs · Accepted Answer · 2013-01-17 20:47:20Z

3

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')
    )

answered Jan 17, 2013 at 20:47

bobs

22.3k12 gold badges71 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2013-01-17 21:27:45Z

3

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.

edited Jan 17, 2013 at 21:27

answered Jan 17, 2013 at 20:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

Pertinent Observer Over a year ago

I need to pass that xml string to another Stored Procedure. How do I set that XML to a nvarchar variable?

marc_s Over a year ago

@PertinentObserver: added another sub-select after the CTE to store the result into a nvarchar(max) variable. But since this is really pure XML - why aren't you just using the XML datatype?? Would make a lot more sense than nvarchar(max) ...

Collectives™ on Stack Overflow

SET XML EXPLICIT QUERY TO VARIABLE

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related