sql query to get xml output in certain nodes

Question

Post reply

sql query to get xml output in certain nodes

rshrestha510

SSC Enthusiast

Points: 188
More actions
October 14, 2022 at 9:16 pm

#4099789
I have table like this
create table CarModel
(Brand varchar(200),
Model varchar(500),
Color varchar(100),
Price money,
TotalQty int,
SequenceNum int)
Insert into CarModel
(Brand, Model,Color, Price,TotalQty,SequenceNum)
values
('Nissan','Quest','Smoke',25000,25,2),
('Nisaan','Leaf','Black',21000,10,1),
('Honda','Civic','Red',25000,10,1),
('Toyota','Camry','White',30000,12,2),
('Toyota','Corolla','Green',12000,30,1),
('Hundai','Sonata','Red',23000,9,1),
('Nisaan','Rouge','Silver',32000,15,3)
and like to get the xml out like below
Update Database="Warehouse" CountStartDate="20230225" CountEndDate="20230302"
<exhibits Brand="Nissan" Count="3"> -- would like to get this on top and only one for each Brand if the count is more than 1
<exhibit Model ="Leaf" ,color="Black",price="21000">
<TotalQty=10>
</exhibit>
<exhibit Model ="Quest" ,color="Smoke",price="25000",>
<TotalQty=25>
</exhibit>
<exhibit Model ="Rouge" ,Color="Silver",price="32000",> --Need to have by SequenceNum
<TotalQty=15>
</exhibit>
</exhibits>
<exhibits Brand="Toyota" Count="2">-- would like to get this on top and only one for each Brand if the count is more than 1
<exhibit Model ="Corolla" ,Color="Green",price="12000">
<TotalQty=30>
</exhibit>
<exhibit Model ="Camry" ,Color="White",price="30000">
<TotalQty=12>
</exhibit>
</exhibits>
<exhibits Brand="Honda" Count="1">
<exhibit Model ="Civic" ,Color="Red",price="25000">
<TotalQty=10>
</exhibit>
<exhibits Brand="Hundai" Count="3">-- would like to get this on top and only one for each Brand if the count is more than 1
<exhibit Model ="Sonata" ,Color="Red",price="23000">
<TotalQty=9>
</exhibit>
</exhibits>
Please help. Thank you
- This topic was modified 2 years, 5 months ago by rshrestha510.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

The problem is straightforward, but we need your help in order to help you.

😎

Can you please post the DDL (create table) script, sample data as an insert statement and the expected results?

rshrestha510 SSC Enthusiast Points: 188 More actions · Answer 2

Sorry for the late reply.

I have table like this

create table CarModel

(Brand varchar(200),

Model varchar(500),

Color varchar(100),

Price money,

TotalQty int,

SequenceNum int)

Insert into CarModel

(Brand, Model,Color, Price,TotalQty,SequenceNum)

values

('Nissan','Quest','Smoke',25000,25,2),

('Nisaan','Leaf','Black',21000,10,1),

('Honda','Civic','Red',25000,10,1),

('Toyota','Camry','White',30000,12,2),

('Toyota','Corolla','Green',12000,30,1),

('Hundai','Sonata','Red',23000,9,1),

('Nisaan','Rouge','Silver',32000,15,3)

and like to get the xml out like below

Update Database="Warehouse" CountStartDate="20230225" CountEndDate="20230302"

<exhibits Brand="Nissan" Count="3"> -- would like to get this on top and only one for each Brand if the count is more than 1

<TotalQty=10>

</exhibit>

<TotalQty=25>

</exhibit>

<exhibit Model ="Rouge" ,Color="Silver",price="32000",> --Need to have by SequenceNum

<TotalQty=15>

</exhibit>

</exhibits>

<exhibits Brand="Toyota" Count="2">-- would like to get this on top and only one for each Brand if the count is more than 1

<TotalQty=30>

</exhibit>

<TotalQty=12>

</exhibit>

</exhibits>

<TotalQty=10>

</exhibit>

<exhibits Brand="Hundai" Count="3">-- would like to get this on top and only one for each Brand if the count is more than 1

<TotalQty=9>

</exhibit>

</exhibits>

Please help. Thank you

drew.allen SSC Guru Points: 77014 More actions · Answer 3

First, when you post code, you should use the {;} Code button to format the code.

The simplest (but not necessarily most efficient) method is to use nested queries.

WITH Brands AS
(
    SELECT cm.Brand
         , COUNT(*) AS Cnt
    FROM #CarModel AS cm
    GROUP BY cm.Brand
)
SELECT b.Brand AS [@Brand]
     , b.Cnt AS [@Count]
     , d.doc AS [*]
FROM Brands AS b
CROSS APPLY
(
    SELECT cm.Model AS [@Model]
 , cm.Color AS [@Color]
 , cm.Price AS [@Price]
 , cm.TotalQty
    FROM #CarModel AS cm
    WHERE cm.Brand = b.Brand
    FOR XML PATH ('exhibit'), TYPE
) AS d(doc)
FOR XML PATH('exhibits')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA