Need Help in XML Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help in XML Query

Author

Topic

jyothi_jayanth
Starting Member

7 Posts

Posted - 2009-09-30 : 19:33:25

Hi I have some tables like below

Reservation
------------
ReservationID
AccountID
PassengerID

ReservationLocations
-------------------
ReservationID
LocationType
Address
City
State
Country

Reservation Charges
-------------------
ReservationID
ChargeType
Amount

AccountMaster
------------
AccountID
AccountName
Phone
Email

PassengerMaster
--------------
PassngerID
PassngerName
PassngerPhone
PassengerEmail

so i need to generate one single XML with below format

<Reservations>
<Reservation>
ReservationID="1234"
<AccountDetails>
AccountName = "XYZ"
Email = "Test@email.com"
</AccountDetails>
<PassngerDetails>
PassngerName = "yyy"
Email = "Test@email.com"
</PassngerDetails>
<Locations>
<Location>
LocationType = "PickUp"
Address = "XYZ"
</Location>
<Location>
LocationType = "Drop-Off"
Address = "YZC"
</Location>
</Locations>
<Charges>
<Charge>
ChargeType ="Base Rate"
Amount = "100.30"
</Charge>
<Charge>
ChargeType ="Toll"
Amount = "10.30"
</Charge>
</Charges>
</Reservation>
-----
-----
</Reservations>

please help me in building the above XML query

your help is greatly appreciated

Thanks in advance

Jayanthu Babu gadde

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:30:36

have a look at syntax of FOR XML PATH in books online

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:39:51

also
http://geekswithblogs.net/chavansachinr/archive/2007/04/30/112132.aspx

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-01 : 02:40:52

And more specifically,

create View VReservation as
select
R.ReservationID ,
A.AccountName,
A.Email,
P.PassngerName,
P.PassengerEmail,
RL.LocationType,
RL.Address,
RC.ChargeType,
RC.Amount
From
Reservation R,
ReservationLocations RL,
ReservationCharges RC,
AccountMaster A,
PassengerMaster P
where
R.ReservationID=RL.ReservationID AND
R.ReservationID=RC.ReservationID AND
R.AccountID=A.AccountID AND
R.PassengerID=P.PassngerID

SELECT
ReservationID,
(
SELECT
AccountName,
Email
FROM
VReservation b
WHERE
a.ReservationID = b.ReservationID
GROUP BY
ReservationID,
AccountName,
Email
FOR XML PATH('AccountDetails'),TYPE
),
(
SELECT
PassngerName,
PassengerEmail
FROM
VReservation b1
WHERE
a.ReservationID=b1.ReservationID
GROUP BY
ReservationID,
PassngerName,
PassengerEmail
FOR XML PATH('PassngerDetails'),TYPE
),
(
SELECT
LocationType,
Address
FROM
VReservation b2
WHERE
a.ReservationID=b2.ReservationID
GROUP BY
ReservationID,
LocationType,
Address
FOR XML PATH('Locations'),TYPE
),
(
SELECT
ChargeType,
Amount
FROM
VReservation b2
WHERE
a.ReservationID=b2.ReservationID
GROUP BY
ReservationID,
ChargeType,
Amount
FOR XML PATH('Charges'),TYPE
)
FROM
VReservation a
GROUP BY
ReservationID
FOR XML PATH('Reservations'),ROOT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources