SQL XML creation using query

Question

I am trying to generate XML file code using SQL from Database which has two columns with Duplicate Value and one column with different values. I used code similar to this

DECLARE @SelectXML bit = 1 SELECT @XMLResult = convert(varchar(max),( SELECT distinct max(Num1) 'Num1' , max(Num2) 'Num2' ,(case when Side='1' then 'L' else 'R' end) 'Num3/Side' ,(case when Side='1' then 'L' else 'R' end ) 'Num3/Side' From(select Num1,Num2,Num3 Table1) A group by Side

FOR XML PATH(''),TYPE, ROOT('rootnode') ))

IF @SelectXML = 1 BEGIN SELECT convert(xml,@XMLResult) END Print @XMLResult

which gives result like this

<?xml version="1.0"?>

-<rootnode>

<Num1>200</Num1>

<Num2>260.8000</Num2>


-<Num3>

<Side>LL</Side>

</Num3>

<Num1>200</Num1>

<Num2>260.8000</Num2>


-<Num3>

<Side>RR</Side>

</Num3>

</rootnode>

I want last Num3 only one column and with Two rows like

<?xml version="1.0"?>

-<rootnode>

<Num1>200</Num1>

<Num2>260.8000</Num2>


-<Num3>

<Side>L</Side>

<Side>R</Side>

</Num3>

</rootnode>

is it possible to add two rows in one column in XML with SQL query

Answer 1

You need to have another for xml subquery for your Num3.

DECLARE @XMLResult varchar(max)
DECLARE @SelectXML bit = 1 
SELECT @XMLResult = convert(varchar(max)
    ,( SELECT max(Num1) 'Num1' 
    , max(Num2) 'Num2' 
    , (select 
         max(case when t.Side='1' then 'L' else 'R' end) 'Side'
         From Table1 t
         Where t.Num1 = max(A.Num1) or t.Num2 = max(A.Num2)
         Group by t.Num1, t.Num2
        FOR XML PATH(''), TYPE) 'Num3'
    From Table1 A 

FOR XML PATH(''),TYPE, ROOT('rootnode') ))

IF @SelectXML = 1 BEGIN SELECT convert(xml,@XMLResult) END Print @XMLResult