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

You need to provide a minimal, reproducible example while asking a question. Please refer to the following link: stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table() plus INSERT statements. (2) What you need to do, i.e. logic, and your code implementation of it. (3) Desired output based on the sample data. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jan 21, 2020 at 3:36

Ed Bangga · Accepted Answer · 2020-01-21 02:14:01Z

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

answered Jan 21, 2020 at 2:14

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

SQL XML creation using query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related