XML Query Question - 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 2008 Forums

Transact-SQL (2008)

XML Query Question

Author

Topic

sundevilscott
Starting Member

12 Posts

Posted - 2011-11-07 : 11:02:14

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>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 11:18:33

instead of subquery add them to main query using CROSS APPLY and dont use FOR XML PATH

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sundevilscott
Starting Member

12 Posts

Posted - 2011-11-07 : 12:16:37

Thank you for the response, I am new to SQL XML queries can you give me an example of what you are referring to please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 12:20:00

if you can provide data in below format, i will able to give suggestion

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sundevilscott
Starting Member

12 Posts

Posted - 2011-11-07 : 15:52:18

First of all thank you so much for your response.

These 3 tables are the only ones needed for this example. The join is as follows.

cruise_bookings cb
INNER JOIN cruise_passengers cp ON cp.cruisebookrecno = cb.recno
INNER JOIN names n ON cp.namerecno = n.recno

Tables to create cruise_bookings, cruise_passengers, names
____________________________________________________________________________________________________

CREATE TABLE [dbo].[cruise_bookings](
[recno] [int] NOT NULL,
[cobrandrecno] [int] NOT NULL,
[subcobrandid] [varchar](20) NULL,
[customerrecno] [int] NOT NULL,
[namerecno] [int] NOT NULL,
[promocoderecno] [int] NULL,
[gdsrecno] [int] NULL,
[sailingrecno] [int] NULL,
[cruisespecialrecno] [int] NULL,
[cruiselinerecno] [int] NOT NULL,
[shiprecno] [int] NOT NULL,
[destinationrecno] [int] NOT NULL,
[departureportrecno] [int] NOT NULL,
[arrivalportrecno] [int] NOT NULL,
[sailingdatefrom] [datetime] NOT NULL,
[sailingdatethru] [datetime] NOT NULL,
[numberofdays] [int] NOT NULL,
[categoryrecno] [int] NOT NULL,
[cabinnumber] [varchar](10) NOT NULL,
[bookingdate] [datetime] NOT NULL,
[bookingnumber] [varchar](50) NULL,
[relationnumber] [varchar](50) NULL,
[typeofpurchaserecno] [int] NULL,
[groupnumber] [varchar](50) NULL,
[groupstatus] [int] NULL,
[groupaccommodationrecno] [int] NULL,
[diningpreference] [varchar](30) NULL,
[dinewith] [varchar](50) NULL,
[travelwith] [varchar](50) NULL,
[bathtype] [varchar](50) NULL,
[tablesize] [varchar](30) NULL,
[pnrrecloc] [varchar](15) NULL,
[bookingfarecode] [varchar](10) NULL,
[pricingfarecode] [varchar](10) NULL,
[pseudo] [varchar](20) NULL,
[purchasedinsurance] [int] NOT NULL,
[typeofinsurancerecno] [int] NULL,
[fulfillmenturl] [nvarchar](100) NULL,
[policynumber] [nvarchar](50) NULL,
[amountinsured] [money] NULL,
[cancelagentname] [varchar](50) NULL,
[cancelrefnum] [varchar](50) NULL,
[canceldate] [datetime] NULL,
[cancelledby] [int] NULL,
[cancellingpassenger] [varchar](50) NULL,
[cancelcoveredbyins] [int] NULL,
[totalcharges] [money] NOT NULL,
[cost] [money] NOT NULL,
[profit] [money] NOT NULL,
[certvalue] [money] NULL,
[adjustedprofit] [money] NULL,
[processingfees] [money] NOT NULL,
[fuelsurcharge] [money] NULL,
[cancellationfees] [money] NOT NULL,
[insurancecharges] [money] NOT NULL,
[insurancecost] [money] NULL,
[insuranceprofit] [money] NULL,
[packageid] [varchar](50) NULL,
[finalpaymentdate] [datetime] NULL,
[currencyrecnopassenger] [int] NOT NULL,
[currencyrecnocruiseline] [int] NOT NULL,
[currencyrecnoinsurance] [int] NOT NULL,
[userrecno] [int] NOT NULL,
[departmentrecno] [int] NULL,
[officerecno] [int] NOT NULL,
[agentinitials] [varchar](5) NULL,
[mrpointsburned] [int] NULL,
[mrpointsbalance] [int] NULL,
[status] [int] NOT NULL,
[approvedbyqa] [int] NULL,
[currentlyediting] [int] NULL,
[currentlyeditingby] [int] NULL,
[currentlyeditingdate] [datetime] NULL,
[editedby] [int] NOT NULL,
[editeddate] [datetime] NOT NULL,
[cabinconfig] [text] NULL,
[beddingconfig] [varchar](50) NULL,
[tpunumber] [varchar](20) NULL,
[amexcardtype] [varchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
_________________________________________________________________________________________________________

CREATE TABLE [dbo].[cruise_passengers](
[recno] [int] NOT NULL,
[cruisebookrecno] [int] NOT NULL,
[namerecno] [int] NOT NULL,
[passengernumber] [int] NOT NULL,
[passedpassnumber] [varchar](30) NULL,
[cruisefare] [money] NOT NULL,
[upgradefee] [money] NOT NULL,
[portcharges] [money] NOT NULL,
[govtfees] [money] NOT NULL,
[airfare] [money] NOT NULL,
[airporttaxes] [money] NOT NULL,
[airdeparturecity] [nvarchar](60) NULL,
[airdestinationcity] [nvarchar](60) NULL,
[transportationtyperecno] [int] NULL,
[transportationdirectionrecno] [int] NULL,
[packagefees] [money] NOT NULL,
[cruiselineinsurance] [money] NOT NULL,
[editedby] [int] NOT NULL,
[editeddate] [datetime] NOT NULL,
CONSTRAINT [IX_cruise_passengers] UNIQUE NONCLUSTERED
(
[cruisebookrecno] ASC,
[namerecno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [INDEXES]
) ON [PRIMARY]

___________________________________________________________________________________________________________

CREATE TABLE [dbo].[names](
[recno] [int] NOT NULL,
[customerrecno] [int] NOT NULL,
[nametyperecno] [int] NOT NULL,
[firstname] [nvarchar](90) NULL,
[firstnamealluppercase] [nvarchar](90) NULL,
[middlename] [nvarchar](90) NULL,
[lastname] [nvarchar](90) NULL,
[lastnamealluppercase] [nvarchar](90) NULL,
[fullname] [nvarchar](130) NULL,
[fullnamealluppercase] [nvarchar](130) NULL,
[title] [int] NULL,
[gender] [nvarchar](15) NULL,
[mainphone] [nvarchar](125) NULL,
[mainphone2] [nvarchar](125) NULL,
[mainext] [nvarchar](125) NULL,
[altphone] [nvarchar](125) NULL,
[altext] [nvarchar](125) NULL,
[fax] [nvarchar](125) NULL,
[cellphone] [nvarchar](125) NULL,
[pager] [nvarchar](125) NULL,
[email] [nvarchar](150) NULL,
[emailalluppercase] [nvarchar](150) NULL,
[emailoptin] [int] NULL,
[password] [nvarchar](50) NULL,
[typeofemail] [int] NULL,
[company] [nvarchar](120) NULL,
[address1] [nvarchar](150) NULL,
[address2] [nvarchar](150) NULL,
[address3] [nvarchar](150) NULL,
[city] [nvarchar](90) NULL,
[state] [nvarchar](30) NULL,
[zip] [nvarchar](35) NULL,
[zip2] [nvarchar](35) NULL,
[countryrecno] [int] NULL,
[staterecno] [int] NULL,
[dateofbirth] [datetime] NULL,
[citizenshiprecno] [int] NULL,
[passport] [nvarchar](150) NULL,
[issuingcountry] [int] NULL,
[expdate] [datetime] NULL,
[soundexfirstname] [char](4) NULL,
[soundexlastname] [char](4) NULL,
[iscontactvalid] [int] NULL,
[editedby] [int] NOT NULL,
[editeddate] [datetime] NOT NULL
) ON [PRIMARY]

_____________________________________________________________________________________
data for cruise_bookings

INSERT INTO customer..cruise_bookings
VALUES ( 1439075 -- recno - int
, 2053192 -- cobrandrecno - int
, '' -- subcobrandid - varchar(20)
, 6532027-- customerrecno - int
, 12846760-- namerecno - int
, null-- promocoderecno - int
, 5-- gdsrecno - int
, 238542-- sailingrecno - int
, 22861660-- cruisespecialrecno - int
, 4-- cruiselinerecno - int
, 79-- shiprecno - int
, 8-- destinationrecno - int
, 25-- departureportrecno - int
, 25-- arrivalportrecno - int
, '2013-02-10 00:00:00.000'-- sailingdatefrom - datetime
, '2013-02-17 00:00:00.000'-- sailingdatethru - datetime
, 7-- numberofdays - int
, 1126-- categoryrecno - int
, 2598-- cabinnumber - varchar(10)
, '2011-10-27 04:39:57.047'-- bookingdate - datetime
, 6757054-- bookingnumber - varchar(50)
, ''-- relationnumber - varchar(50)
, 32-- typeofpurchaserecno - int
, ''-- groupnumber - varchar(50)
, 1-- groupstatus - int
, 0-- groupaccommodationrecno - int
, 69-- diningpreference - varchar(30)
, ''-- dinewith - varchar(50)
, '' -- travelwith - varchar(50)
, '' -- bathtype - varchar(50)
, '' -- tablesize - varchar(30)
, '' -- pnrrecloc - varchar(15)
, 'NEB'-- bookingfarecode - varchar(10)
, 'LAF'-- pricingfarecode - varchar(10)
, 'LONI33100'-- pseudo - varchar(20)
, 0-- purchasedinsurance - int
, 0-- typeofinsurancerecno - int
, null-- fulfillmenturl - nvarchar(100)
, ''-- policynumber - nvarchar(50)
, '0.00'-- amountinsured - money
, ''-- cancelagentname - varchar(50)
, ''-- cancelrefnum - varchar(50)
, null-- canceldate - datetime
, 0-- cancelledby - int
, ''-- cancellingpassenger - varchar(50)
, 0-- cancelcoveredbyins - int
, '378.52'-- totalcharges - money
, '908.52'-- cost - money
, '-530.00'-- profit - money
, '774.00'-- certvalue - money
, '224.00'-- adjustedprofit - money
, '0.00'-- processingfees - money
, null-- fuelsurcharge - money
, '0.00'-- cancellationfees - money
, '0.00'-- insurancecharges - money
, '0.00'-- insurancecost - money
, '0.00'-- insuranceprofit - money
, 'AD01130210AD07D088'-- packageid - varchar(50)
, '2011-10-27 00:00:00.000'-- finalpaymentdate - datetime
, 2-- currencyrecnopassenger - int
, 2-- currencyrecnocruiseline - int
, 2-- currencyrecnoinsurance - int
, 9409-- userrecno - int
, 135-- departmentrecno - int
, 28-- officerecno - int
, ''-- agentinitials - varchar(5)
, 0-- mrpointsburned - int
, 0-- mrpointsbalance - int
, 2-- status - int
, 1-- approvedbyqa - int
, 0-- currentlyediting - int
, null-- currentlyeditingby - int
, null-- currentlyeditingdate - datetime
, 9409-- editedby - int
, '2011-10-27 05:04:11.090'-- editeddate - datetime
, null-- cabinconfig - text
, ''-- beddingconfig - varchar(50)
, null-- tpunumber - varchar(20)
, ''-- amexcardtype - varchar(50)
)
__________________________________________________________________________________________________________
data for cruise_passengers

INSERT INTO dbo.cruise_passengers

VALUES (3046345 -- recno - int
, 1439075 -- cruisebookrecno - int
, 12846760 -- namerecno - int
, 1 -- passengernumber - int
, '' -- passedpassnumber - varchar(30)
, '0.00' -- cruisefare - money
, '0.00' -- upgradefee - money
, '110.00' -- portcharges - money
, '28.00' -- govtfees - money
, '0.00' -- airfare - money
, '0.00' -- airporttaxes - money
, 'LHR' -- airdeparturecity - nvarchar(60)
, '' -- airdestinationcity - nvarchar(60)
, 0 -- transportationtyperecno - int
, 0 -- transportationdirectionrecno - int
, '51.26' -- packagefees - money
, '0.00' -- cruiselineinsurance - money
, 9409 -- editedby - int
, '2011-10-27 05:04:11.093' -- editeddate - datetime
)

_____________________________________________________________________________________________________________
Data for Names

INSERT INTO dbo.names
VALUES ( 12846760 -- recno - int
, 6532027 -- customerrecno - int
, 1 -- nametyperecno - int
, 'John' -- firstname - nvarchar(90)
, 'JOHN' -- firstnamealluppercase - nvarchar(90)
, '' -- middlename - nvarchar(90)
, 'Smith' -- lastname - nvarchar(90)
, 'SMITH' -- lastnamealluppercase - nvarchar(90)
, 'John Smith' -- fullname - nvarchar(130)
, 'JOHN SMITH' -- fullnamealluppercase - nvarchar(130)
, 2 -- title - int
, 'M' -- gender - nvarchar(15)
, '' -- mainphone - nvarchar(125)
, '' -- mainphone2 - nvarchar(125)
, '' -- mainext - nvarchar(125)
, '' -- altphone - nvarchar(125)
, '' -- altext - nvarchar(125)
, '' -- fax - nvarchar(125)
, '' -- cellphone - nvarchar(125)
, '' -- pager - nvarchar(125)
, '' -- email - nvarchar(150)
, '' -- emailalluppercase - nvarchar(150)
, '' -- emailoptin - int
, '' -- password - nvarchar(50)
, ''-- typeofemail - int
, ''-- company - nvarchar(120)
, '1 Avenue Place' -- address1 - nvarchar(150)
, 'Tillington'-- address2 - nvarchar(150)
, ''-- address3 - nvarchar(150)
, 'Petworth West Sussex'-- city - nvarchar(90)
, ''-- state - nvarchar(30)
, 'GU28 9AE'-- zip - nvarchar(35)
, 'GU289AE'-- zip2 - nvarchar(35)
, 37-- countryrecno - int
, NULL-- staterecno - int
, '1957-08-15 00:00:00.000'-- dateofbirth - datetime
, 37-- citizenshiprecno - int
, ''-- passport - nvarchar(150)
, '-99'-- issuingcountry - int
, '2001-01-01 00:00:00.000'-- expdate - datetime
, 'W450'-- soundexfirstname - char(4)
, 'B636'-- soundexlastname - char(4)
, 1-- iscontactvalid - int
, 9409-- editedby - int
, '2011-10-27 05:04:11.093'-- editeddate - datetime
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 00:23:31

i think reason is because you've named nodes same. try with


DECLARE @cruisebookrecno INT
, @last_update_date DATETIME

SET @cruisebookrecno = 1439075 -- 1425311
SET @last_update_date = '01/01/1900' 

SELECT
(SELECT n.address1 "Address/AddressLine1"
, n.address2 "Address/AddressLine2"
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')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sundevilscott
Starting Member

12 Posts

Posted - 2011-11-08 : 09:49:17

The site that it is being uploaded to doesn't accept the 1 or 2 it only accepts the addressline nodes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources