XML Query Question
-
What I need to do is to pull back the AddressLine 1 and 2 but they both need to go in to nodes called AddressLine. Below is the query, it has all my joins but this is just one snipet of the code.
___________________________________________________________________________
DECLARE @cruisebookrecno INT
, @last_update_date DATETIME
SET @cruisebookrecno = 1439075 -- 1425311
SET @last_update_date = '01/01/1900'
SELECT
(SELECT n.address1 "Address/AddressLine"
, n.address2 "Address/AddressLine"
FROM customer..cruise_bookings cb
INNER JOIN customer..cruise_passengers cp ON cp.cruisebookrecno = cb.recno
INNER JOIN customer..names n ON cp.namerecno = n.recno
WHERE cb.recno = @cruisebookrecno
AND cp.passengernumber = 1
FOR XML PATH (''), TYPE) "CustomerForBooking/DirectCustomer/Customer/AddressInfo"
FROM customer..cruise_bookings cb
INNER JOIN content..destinations_text dt ON cb.destinationrecno = dt.destinationrecno AND dt.languagerecno = 1
INNER JOIN customer..users u ON cb.userrecno = u.recno
INNER JOIN customer..offices o ON u.officerecno = o.recno
INNER JOIN customer..names n ON cb.namerecno = n.recno
INNER JOIN customer..name_titles nt ON n.title = nt.recno
INNER JOIN customer..customers c ON cb.customerrecno = c.recno
INNER JOIN CONTENT..gds gds ON cb.gdsrecno = gds.recno
INNER JOIN customer..cruise_passengers cp ON cb.recno = cp.cruisebookrecno
INNER JOIN CONTENT..categories_text cat ON cb.categoryrecno = cat.categoryrecno AND cat.languagerecno = 1
INNER JOIN content..ports_of_call poc ON cb.arrivalportrecno = poc.recno
INNER JOIN content..countries_text ct ON poc.countryrecno = ct.countryrecno
INNER JOIN content..countries_text ct2 ON n.countryrecno = ct2.countryrecno
INNER JOIN content..ships_text ship ON cb.shiprecno = ship.shiprecno
INNER JOIN (
SELECT
cbth.cruisebookrecno,
MIN(cbth.approveddate) min_approveddate
FROM customer..cruise_booking_transaction_history cbth
WHERE cbth.approveddate IS NOT NULL
GROUP BY
cbth.cruisebookrecno
HAVING
MIN(cbth.approveddate) > @last_update_date) trans
ON cb.recno = trans.cruisebookrecno
INNER JOIN content..cobrand_partners cbp ON cb.cobrandrecno = cbp.recno
LEFT JOIN reports..dolphin_membertype_xref_webuser dmxw ON dmxw.cobrandrecno = cbp.recno
LEFT JOIN reports..dolphin_membertype_xref_webuser dmxn ON dmxn.cobrandrecno = cbp.recno
LEFT JOIN reports..membermaint_members rmm ON c.customerid = rmm.member_id
INNER JOIN content..cruise_lines_text clt ON cb.cruiselinerecno = clt.cruiselinerecno AND clt.languagerecno = 1
INNER JOIN content..cruise_lines_dolphin_xref cldx ON cb.cruiselinerecno = cldx.cruise_line_recno AND cldx.languagerecno = 1
LEFT JOIN content..ports_of_call_dolphin pcd ON cb.departureportrecno = pcd.portofcallrecno OR cb.arrivalportrecno = pcd.portofcallrecno
WHERE dt.languagerecno = 1
AND cb.recno = @cruisebookrecno
AND cp.passengernumber = 1
--AND cb.editeddate > (SELECT MAX(last_date_updated) FROM reports..XML_Export) -- (select GETDATE()-1)
FOR XML PATH ('TravelFolder')
______________________________________________________________________________________
Here is the results in XML
<TravelFolder>
<CustomerForBooking>
<DirectCustomer>
<Customer>
<AddressInfo>
<Address>
<AddressLine>One Park TerraceTillington</AddressLine>
</Address>
</AddressInfo>
</Customer>
</DirectCustomer>
</CustomerForBooking>
</TravelFolder>
Here is what I am looking for:
__________________________________________________________________________________
<TravelFolder>
<CustomerForBooking>
<DirectCustomer>
<Customer>
<AddressInfo>
<Address>
<AddressLine>One Park Terrace</AddressLine>
<AddressLine>Tillington</AddressLine>
</Address>
</AddressInfo>
</Customer>
</DirectCustomer>
</CustomerForBooking>
</TravelFolder>
-
Would this work:
select isnull(addressline1, '') + isnull(addressline2,'') "Address/AddressLine"....[rest of sql]
-
Sorry -- didnt read your post close enough. My suggestion is goingto give u what u are already getting... Ill take a closer look, send a solutionif i have one
-
Try changing
SELECT(SELECT n.address1 "Address/AddressLine"
, n.address2 "Address/AddressLine"
FROM customer..cruise_bookings cb
to
SELECT(SELECT
(SELECT Address FROM (SELECT n.address1 UNION ALL n.address2) d(Address) FOR XML PATH('AddressLine'),TYPE)
FROM customer..cruise_bookings cb
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply