For XML Path - 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)

For XML Path

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-11-05 : 12:02:44

I have a query:

CROSS APPLY(SELECT Lease + ' / ' AS [text()]
FROM DATA t
WHERE Date=t.Date AND WorkLocation=t.WorkLocation
FOR XML PATH(''))tl(txtlist)

that returns:

Lease SONumber
DUNN A 1-H / UMBERSON A 1-H / 50950
DUNN A 1-H / UMBERSON A 1-H / 50848

I want to also have this concantenation used on the SONumber column to where the results would be:

Lease SONumber
DUNN A 1-H / UMBERSON A 1-H / 50950 / 50848 /

I have tried to add this second concantenation column and I keep getting errors. Can this be done? Thanks for any help.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-05 : 12:31:05

can you please show us how you are doing it that causes error

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

osupratt
Posting Yak Master

238 Posts

Posted - 2009-11-05 : 13:06:15

SELECT
Date,WorkLocation,RigNumber,Customer,RTRIM(tl.txtlist) AS Lease,
RTRIM(tl.txtlist1) AS SONumber1,SONumber,
TargetHrsDay,StandardHrsDay,UtilHours,CostCenter,BilledDollars,BilledHours

FROM DATA

CROSS APPLY(SELECT Lease + ' / ' AS [text()], SONumber +' / ' AS [text()]
FROM DATA t
WHERE Date=t.Date AND WorkLocation=t.WorkLocation
FOR XML PATH(''))tl(txtlist), tl(txtlist1)

this gives me an error:
Invalid object name 'tl'.

i have the concantenation working for one column (combining mulitple 'Lease' names in column Lease. i want to do the same thing for the column SONumber. i don't want the Lease and the SONumber data combined though (i've gotten that result also).

thanks for your reply.

thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-05 : 14:48:54

please provide post like this next time, with data and all for others to test your stuff.
Is this what you are looking for ?
DUNN A 1-H UMBERSON A 1-H/ 50950 / 50951 / 50952 / 50953 / 50954 / 50955 / 50956 / 50957 / 50958 / 50959 / 50960


CREATE TABLE #DATA(Date smalldatetime, WorkLocation varchar(50), RigNumber varchar(50), Customer varchar(50), txtList varchar(50), txtlist1 varchar(50), SONumber varchar(50), 
                   TargetHrsDay int, StandardHrsDay int, UtilHours int, CostCenter varchar(50), BilledDollars money, BilledHours int)

INSERT INTO #DATA
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50950', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50951', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50952', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50953', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50954', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50955', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50956', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50957', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50958', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50959', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50960', 12, 8, 8, 'Kaching', 10000, 9

SELECT RTRIM(txtlist), txtlist1 + STUFF((SELECT ' / ' + SONumber
 
                FROM #DATA
               WHERE Date = t.Date
                 AND WorkLocation = t.WorkLocation
                 FOR XML PATH('')),1,1,'')as [newcol]
FROM #DATA t 
group by RTRIM(txtlist), Date, WorkLocation, txtlist1

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

osupratt
Posting Yak Master

238 Posts

Posted - 2009-11-05 : 16:16:51

i want 2 columns with the For XML Path...
Lease column which i have and it is working and then also SONumber column. i don't want one long field combining both Lease and SONumber.

here is what i currently have that is giving me the error. thanks.

WITH DATA AS(

SELECT
Date,WorkLocation,RigNumber,MAX(Customer)Customer,Lease, SONumber,
MAX(TargetHrsDay)TargetHrsDay,MAX(StandardHrsDay)StandardHrsDay,SUM(UtilHours)UtilHours,
SUM(UtilDollars)UtilDollars,
SUBSTRING(WorkLocation,1,3)+SUBSTRING(RigNumber,5,3) AS CostCenter,SUM(UtilDollars)BilledDollars,
SUM(UtilHours)BilledHours
FROM(
SELECT DISTINCT
hdr.Date,hdr.WorkLocation,hdr.RigNumber,hdr.DailyWorkTicketNumber,hdr.Customer,hdr.Lease,
hdr.WellNumber, hdr.Misc_Text_Field4 AS SONumber,
inv.InvoiceItem, inv.InvoiceItemNumber, inv.Quantity, inv.MiscCode, inv.GLSalesAcct,
inv.UnitOfMeasure, inv.STDUnitPrice, inv.TaxSchedule, inv.TaxClass, inv.TaxRate,

CASE WHEN code.UtilizationDollars ='YES' THEN inv.STDUnitPrice*inv.Quantity ELSE 0 END AS UtilDollars,
CASE WHEN code.UtilizationHours ='YES' THEN inv.Quantity ELSE 0 END AS UtilHours,

CASE
WHEN SUBSTRING(hdr.RigNumber, 1, 2) = 'EM' AND DATENAME(WEEKDAY,hdr.Date)NOT IN ('Saturday','Sunday')
AND hdr.Date NOT IN ('11/26/2009','12/25/2009','1/1/2010') THEN 6
WHEN SUBSTRING(hdr.WorkLocation, 1, 3) = 'MWS' AND DATENAME(WEEKDAY,hdr.Date)NOT IN ('Saturday','Sunday')
AND hdr.Date NOT IN ('11/26/2009','12/25/2009','1/1/2010') THEN 8
WHEN SUBSTRING(hdr.WorkLocation, 1, 3) = 'TWS' AND DATENAME(WEEKDAY,hdr.Date)NOT IN ('Saturday','Sunday')THEN 10
ELSE 0 END AS TargetHrsDay,

CASE
WHEN SUBSTRING(hdr.WorkLocation, 1, 3) = 'MWS' THEN 8
WHEN SUBSTRING(hdr.RigNumber, 1, 2) = 'EM' THEN 6
WHEN SUBSTRING(hdr.WorkLocation, 1, 3) = 'TWS' THEN 10
ELSE 0 END AS StandardHrsDay
FROM dbo.MercerDailyWorkTicketHdr AS hdr INNER JOIN
dbo.MercerDailyWorkTicketInv AS inv ON hdr.DailyWorkTicketNumber = inv.DailyWorkTicketNumber INNER JOIN
MiscCodeUtilization AS code ON inv.MiscCode = code.MiscCode
WHERE hdr.Date='9/12/2009' AND hdr.RigNumber='RIG-113'
) AS X
GROUP BY WorkLocation,RigNumber,Date,Lease,SONumber
)

SELECT
Date,WorkLocation,RigNumber,Customer,RTRIM(tl.txtlist) AS Lease,
RTRIM(tl.txtlist1) AS SONumber1,SONumber,
TargetHrsDay,StandardHrsDay,UtilHours,CostCenter,BilledDollars,BilledHours

FROM DATA

CROSS APPLY(SELECT Lease + ' / ' AS [text()], SONumber +' / ' AS [text()]
FROM DATA t
WHERE Date=t.Date AND WorkLocation=t.WorkLocation
FOR XML PATH(''))tl(txtlist), tl(txtlist1)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-06 : 10:38:59

your requirements change every time you post..what is the final XML format you want? answer this question first


<Root>
 <Lease>DUNN A 1-H</Lease>
 <SONumber>50950</SONumber>
</Root>

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

osupratt
Posting Yak Master

238 Posts

Posted - 2009-11-06 : 11:14:36

I apologize for not being clear. First time using the For XML Path.

I can probably explain this easiest by showing you the following results:

Date Lease Customer SONumber
10/1/2009 ABC TestCust 123
10/1/2009 ABC TestCust 456
10/1/2009 DEF TestCust 123

Now I want to Group this by Date and Customer and have the results be:

Date Lease Customer SONumber
10/1/2009 ABC / DEF TestCust 123 / 456

Not sure how to achieve this. I saw the For XML Path and thought this would work best. I have it working on the Lease column, but don't know how to do this for 2 columns within the same row record. Thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-06 : 11:52:37

ok good I see what you are trying to do, the problem is with CROSS APPLY I believe. I don;t knwo if you can specify more than one column

CROSS APPLY () tl(field1),tl(field2) there probably is a way. that tl is the alias for the sub query you are trying to CROSS APPLY to.

but here is another approach that does work, maybe you can accomplish what you are trying to do with that.


CREATE TABLE #DATA(Date smalldatetime, WorkLocation varchar(50), RigNumber varchar(50), Customer varchar(50),
                   txtList varchar(50), txtlist1 varchar(50), SONumber varchar(50), 
                   TargetHrsDay int, StandardHrsDay int, UtilHours int, CostCenter varchar(50), 
                   BilledDollars money, BilledHours int)

INSERT INTO #DATA
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50951', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1914', 'NW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50952', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1910', 'NE Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50953', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1910', 'NE Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50954', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1911', 'SE Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50955', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1911', 'SE Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50956', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1912', 'SW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50957', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1912', 'SW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50958', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1913', 'MW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50959', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1913', 'MW Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50960', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1915', 'DS Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50961', 12, 8, 8, 'Kaching', 10000, 9
UNION
SELECT GETDATE(), 'Dock 1', '1915', 'DS Riggers', 'DUNN A 1-H', 'UMBERSON A 1-H', '50962', 12, 8, 8, 'Kaching', 10000, 9

SELECT Date, 
       STUFF((SELECT ' / ' + Customer
 
                FROM #DATA a
               WHERE a.Date = t.Date
                 AND a.WorkLocation = t.WorkLocation
                 FOR XML PATH('')),1,1,'') as Customers,
       STUFF((SELECT ' / ' + txtlist
 
                FROM #DATA a
               WHERE a.Date = t.Date
                 AND a.WorkLocation = t.WorkLocation
                 FOR XML PATH('')),1,1,'') as Leases,
       STUFF((SELECT ' / ' + SONumber
 
                FROM #DATA
               WHERE Date = t.Date
                 AND WorkLocation = t.WorkLocation
                 FOR XML PATH('')),1,1,'') as SONumbers
FROM #DATA t 
group by Date, WorkLocation

DROP TABLE #DATA

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources