facing issue in looping through in SOL to XML

Question

Post reply

facing issue in looping through in SOL to XML

info 24557

Grasshopper

Points: 17
More actions
May 31, 2013 at 1:56 am

#275829

We are trying to display the xml code using following query but facing some issue as follows. We have created the temp using various tables in the database and trying to create xml ouput from the same.
CREATE TABLE #crossTable11(
EMPID int INT
, SN Int INT
, EmpName VARCHAR(150)
, EventName VARCHAR(150)
, BookingDate smalldatetime SMALLDATETIME
, PositionName VARCHAR(150)
, DepartmentName VARCHAR(150)
)
INSERT INTO #crossTable11
(EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)
VALUES
(1, 6, 'N1', '99hh', '2013-05-20 00:00:00', 'Manager', 'Admin')
INSERT INTO #crossTable11
(EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)
VALUES
(2, 6, 'N1', 'MS Excel', '', '', '')
INSERT INTO #crossTable11
(EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)
VALUES
values (13, 6, 'N1', 'MS Office', '', '', '')
INSERT INTO #crossTable11
(EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)
VALUES
(4, 2, 'Pkar', '99hh', '2013-05-20 00:00:00', 'Manager', 'Admin')
INSERT INTO #crossTable11
(EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)
VALUES
(5, 2, 'Pkar', 'MS Excel', '2013-05-10 00:00:00', 'Supplier', 'Accounts')
INSERT INTO #crossTable11
(EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)
VALUES
values (6, 2, 'Pkar', 'MS Office', '2013-08-01 00:00:00', 'Supplier', 'Accounts')
SELECT 'Site of Person Involved' AS '@caption', 'SitePI' AS '@fieldName',
(
select EmpName as 'emp/@ID',PositionName as 'emp/@Position',DepartmentName as 'emp/@Department',
(
select EventName as 'event/@ID',BookingDate as 'event/@Date'
from #crossTable11 L
where L.EMPID = p.EMPID
order by EventName
for XML path ('event'),TYPE
)
from #crossTable11 p
group by p.SN,p.EmpName,p.PositionName,p.DepartmentName,p.EMPID
for xml PATH(''), TYPE )
for xml PATH('r'), TYPEOutput is as follows which shows the duplicate names.
<r caption="Site of Person Involved" fieldName="SitePI">
<emp ID="Pkar" Position="" Department="" />
<event>
<event ID="MS Office" Date="1900-01-01T00:00:00" />
</event>
<emp ID="Pkar" Position="Supervisor" Department="Admin" />
<event>
<event ID="99hh" Date="2013-05-20T00:00:00" />
</event>
<emp ID="Pkar" Position="Supervisor" Department="Admin" />
<event>
<event ID="MS Excel" Date="2013-05-10T00:00:00" />
</event>
<emp ID="N1" Position="" Department="" />
<event>
<event ID="99hh" Date="2013-05-20T00:00:00" />
</event>
<emp ID="N1" Position="" Department="" />
<event>
<event ID="MS Office" Date="1900-01-01T00:00:00" />
</event>
<emp ID="N1" Position="Manager" Department="Account" />
<event>
<event ID="MS Excel" Date="1900-01-01T00:00:00" />
</event>
</r>Required output is: We would like to avoid the duplicates and combine the output for them
<r caption="Site of Person Involved" fieldName="SitePI">
<emp ID="Pkar" Position="Supervisor" Department="Admin" />
<event>
<event ID="99hh" Date="2013-05-20T00:00:00" />
<event ID="MS Office" Date="1900-01-01T00:00:00" />
<event ID="MS Excel" Date="2013-05-10T00:00:00" />
</event>
<emp ID="N1" Position="Manager" Department="Account" />
<event>
<event ID="99hh" Date="2013-05-20T00:00:00" />
<event ID="MS Office" Date="1900-01-01T00:00:00" />
<event ID="MS Excel" Date="1900-01-01T00:00:00" />
</event>

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Hi and welcome to the forums.

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

info 24557 Grasshopper Points: 17 More actions · Answer 2

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_V4_Report_AT_AttendeeBookingOverview_1June]

(

@Filters varchar(MAX),

@Company int,

@User int

)

AS

SET ARITHABORT ON

DECLARE @ReportFilters AS xml

SELECT @ReportFilters = ReportFilters FROM Users WHERE UsersID = @User

SELECT @Filters = @ReportFilters.value('(/UserReportSettings/ReportFilters)[1]', 'varchar(MAX)') + ' ' + @ReportFilters.value('(/UserReportSettings/ConsumerReportFilters)[1]', 'varchar(MAX)')

SELECT @Filters = REPLACE(@Filters, '''''', '''')

CREATE TABLE #ReportMaster (

[MU_Attendee_BookingID] [bigint] NOT NULL,

[BookingID] [bigint] NULL,

[MU_Employee_DepartmentID] [bigint] NULL,

[Attendee_Status] [varchar](50) NULL,

[Site] [varchar](50) NULL,

[Department] [varchar](50) NULL,

[Competent] [bit] NULL,

[OwnTime] [bit] NULL,

[AccommodationCost] [smallmoney] NULL,

[VehicleCost] [smallmoney] NULL,

[DateCompetencyAchieved] [smalldatetime] NULL,

[Variations] [int] NULL,

[Feedback] [varchar](400) NULL,

[Notes] [varchar](400) NULL,

[MU_BookingFrequencyID] [bigint] NULL,

[CompanyID] [int] NULL,

[UniqueAttendeeID] [int] NULL,

[CreatedBy] [int] NULL,

[CreatedDate] [smalldatetime] NULL,

[ModifedBy] [int] NULL,

[ModifiedDate] [smalldatetime] NULL,

[Name] [nvarchar](max) null,

[PositionName] [nvarchar](max) null,

[DepartmentName] [nvarchar](max) null,

[EventName] [nvarchar](max) null,

[FirstName] [nvarchar](max) null,

[LastName] [nvarchar](max) null)

INSERT INTO #ReportMaster EXEC usp_AttendanceBookingOverviewReportMaster @Filters,@Company, @User

CREATE TABLE #crossTable11(EMPID int,SN Int,EmpName VARCHAR(150), EventName VARCHAR(150), BookingDate smalldatetime,PositionName VARCHAR(150),DepartmentName VARCHAR(150))

--INSERT INTO #crossTable11 exec usp_AT3

INSERT INTO #crossTable11 (EMPID,SN,EmpName,EventName,BookingDate,PositionName,DepartmentName) values (1,6,'Nick Felton','99hh','2013-05-20 00:00:00','Manager','Admin')

INSERT INTO #crossTable11 (EMPID,SN,EmpName,EventName,BookingDate,PositionName,DepartmentName) values (2,6,'Nick Felton','MS Excel','','','')

INSERT INTO #crossTable11 (EMPID,SN,EmpName,EventName,BookingDate,PositionName,DepartmentName) values (3,6,'Nick Felton','MS Office','','','')

INSERT INTO #crossTable11 (EMPID,SN,EmpName,EventName,BookingDate,PositionName,DepartmentName) values (4,2,'Priyanka Renaweekar','99hh','2013-05-20 00:00:00','Manager','Admin')

INSERT INTO #crossTable11 (EMPID,SN,EmpName,EventName,BookingDate,PositionName,DepartmentName) values (5,2,'Priyanka Renaweekar','MS Excel','2013-05-10 00:00:00','Supplier','Accounts')

INSERT INTO #crossTable11 (EMPID,SN,EmpName,EventName,BookingDate,PositionName,DepartmentName) values (6,2,'Priyanka Renaweekar','MS Office','2013-08-01 00:00:00','Supplier','Accounts')

DECLARE @MinDate smalldatetime, @MaxDate smalldatetime

SELECT @MinDate = MIN(AB.CreatedDate), @MaxDate = MAX(AB.CreatedDate)

FROM #ReportMaster AB

CREATE TABLE #DateRange ([Label] varchar(music) NOT NULL, [MonthVal] [tinyint] NOT NULL, [YearVal] [smallint] NOT NULL)

INSERT INTO #DateRange SELECT * FROM dbo.udf_GetMonthsInDateRange(@MinDate, @MaxDate)

DECLARE @SQL varchar(MAX)

DECLARE @temp int

DECLARE @ReportName varchar(200)

SET @ReportName = 'Sample Report For'

Declare @Query as nvarchar(max)

SELECT 'Site of Person Involved' AS '@caption', 'SitePI' AS '@fieldName',

(

Select Distinct VV.EventName as 'emp/@Name',VV.BookingID as 'emp/@ID'

from #ReportMaster VV

for XML path('eventList'),Type),

(

select EmpName as 'emp/@ID',PositionName as 'emp/@Position',DepartmentName as 'emp/@Department',

(

select distinct EventName,BookingDate

from #crossTable11 L

where L.EMPID = p.EMPID

order by EventName

for XML path ('emp'),TYPE

)

from #crossTable11 p where p.PositionName !='' and p.DepartmentName !=''

group by p.SN,p.EmpName,p.PositionName,p.DepartmentName,p.EMPID

for xml PATH('r'), TYPE),

--Non-Attendees

(SELECT distinct ABA.EventName FROM #ReportMaster BBB

INNER JOIN

Booking ABA ON BBB.BookingID!= ABA.BookingID

for xml PATH(''), TYPE)as 'emp1',

(SELECT DISTINCT E.FirstName + ' ' + E.LastName as 'event/@Name'

,PP.PositionName as 'event/@Position',DD.DepartmentName as 'event/@Department'

--,Ab.BookingID ,b.EventName ,ED.MU_Employee_DepartmentID

FROM Employee E

inner join MU_Employee_Department ED on E.EmployeeID=ED.EmployeeID

inner join Position PP on PP.PositionID = ED.PositionID

inner join Department DD on DD.DepartmentID = ED.DepartmentID

inner join MU_Attendee_Bookings Ab on Ab.MU_Employee_DepartmentID != ED.MU_Employee_DepartmentID

left outer join Booking B on AB.BookingID=B.BookingID where E.EmployeeID NOT IN

(SELECT distinct MU_Employee_Department.EmployeeID FROM MU_Employee_Department,MU_Attendee_Bookings ,

Employee WHERE MU_Employee_Department.MU_Employee_DepartmentID = MU_Attendee_Bookings.MU_Employee_DepartmentID)

for xml PATH(''), TYPE)

FOR XML PATH('r')

--SELECT 1 as Tag,

-- NULL as Parent,

-- R.MU_Employee_DepartmentID as [Customer!1!CustomerID],

-- NULL as [ReportMaster!2!OrderID!element],

-- NULL as [ReportMaster!2!OrderDate]

--FROM #ReportMaster R

--UNION ALL

--SELECT 2,

-- 1,

-- R1.MU_Employee_DepartmentID,

-- CT.BookingDate,

-- CT.EventName

--FROM #ReportMaster R1, #crossTable11 CT

--WHERE R1.MU_Employee_DepartmentID = CT.EMPID

--ORDER BY [Customer!1!CustomerID], [ReportMaster!2!OrderID!element]

--FOR XML EXPLICIT

---exec [usp_V4_Report_AT_AttendeeBookingOverview_1June] ' ',4,1012

The output is as follows:

</emp>

</emp>

</emp>

</r>

What I need as follows. I do not want the name2 to repeat but combine the two outputs related to name2.

</emp>

</emp>

</r>

Any help is really appreciated

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

The issue here is that I can't even come close to executing this on my server. There are a lot of missing pieces here.

Users

udf_GetMonthsInDateRange

Booking

Employee

MU_Employee_Department

Position

Department

MU_Attendee_Bookings

And Varchar(music)???

Probably the easiest way to get to a solution here is to make this an example and not your actual code. Put together a temp table with the raw data that you are trying to convert to xml.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

info 24557 Grasshopper Points: 17 More actions · Answer 4

Thanks for the response and actually sorry for the confusion. I am new to the forum. But the whole issue is

Here is my query

select EmpName as 'emp/@ID',PositionName as 'emp/@Position',DepartmentName as 'emp/@Department',

(

select distinct EventName,BookingDate

from #crossTable11 L

where L.EMPID = p.EMPID

order by EventName

for XML path ('emp'),TYPE

)

from #crossTable11 p where p.PositionName !='' and p.DepartmentName !=''

group by p.SN,p.EmpName,p.PositionName,p.DepartmentName,p.EMPID

for xml PATH('r'), TYPE),

Which is generating following output:

</emp>

</emp>

</emp>

But I am looking for following output to be produced.

</emp>

</emp>

Basically, the output is showing repeated employee names which I would like to avoid.

Any help is greatly appreciated.