Need help in writing a query using FOR XML EXPLICIT

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 2000 Forums

Transact-SQL (2000)

Need help in writing a query using FOR XML EXPLICIT

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-30 : 08:10:35

Senthil writes "You can copy & paste all the following lines till the end into your query analyser for easy understanding.
Please help me, how I can get this output in SQl Server 2000. Also, Whats the problem in my query? I appriciate your immediate responces.

--Here is my table structure & data look like
SET NOCOUNT ON
DECLARE @Dept TABLE (DeptID INT IDENTITY(1,1), DeptName VARCHAR(50))
DECLARE @Emp TABLE (Empid INT IDENTITY(1,1), EmpName VARCHAR(60))
DECLARE @EmpDept TABLE(EmpID INT , DeptID INT)
DECLARE @EmpSal TABLE (EmpID INT, MonthID SMALLINT, Salary MONEY)

INSERT INTO @Dept SELECT 'Engineering'
INSERT INTO @Dept SELECT 'Workshop'
INSERT INTO @Dept SELECT 'Security'
INSERT INTO @Emp SELECT 'Senthil'
INSERT INTO @Emp SELECT 'Ram'
INSERT INTO @Emp SELECT 'Kumar'
INSERT INTO @Emp SELECT 'Shankar'
INSERT INTO @Emp SELECT 'Basha'
INSERT INTO @EmpDept SELECT 1, 1
INSERT INTO @EmpDept SELECT 2, 1
INSERT INTO @EmpDept SELECT 3, 2
INSERT INTO @EmpDept SELECT 4, 3
INSERT INTO @EmpDept SELECT 5, 3

INSERT INTO @EmpSal SELECT 1, 1, 50
INSERT INTO @EmpSal SELECT 1, 2, 60
INSERT INTO @EmpSal SELECT 1, 3, 60
INSERT INTO @EmpSal SELECT 1, 4, 70
INSERT INTO @EmpSal SELECT 1, 5, 50
INSERT INTO @EmpSal SELECT 1, 6, 60
INSERT INTO @EmpSal SELECT 2, 1, 55
INSERT INTO @EmpSal SELECT 2, 2, 64
INSERT INTO @EmpSal SELECT 2, 3, 64
INSERT INTO @EmpSal SELECT 2, 4, 72
INSERT INTO @EmpSal SELECT 2, 5, 55
INSERT INTO @EmpSal SELECT 2, 6, 66

INSERT INTO @EmpSal SELECT 3, 1, 52
INSERT INTO @EmpSal SELECT 3, 2, 624
INSERT INTO @EmpSal SELECT 3, 3, 684
INSERT INTO @EmpSal SELECT 3, 4, 712
INSERT INTO @EmpSal SELECT 3, 5, 535
INSERT INTO @EmpSal SELECT 3, 6, 660

INSERT INTO @EmpSal SELECT 4, 1, 502
INSERT INTO @EmpSal SELECT 4, 2, 64
INSERT INTO @EmpSal SELECT 4, 3, 84
INSERT INTO @EmpSal SELECT 4, 4, 12
INSERT INTO @EmpSal SELECT 4, 5, 53
INSERT INTO @EmpSal SELECT 4, 6, 60

/* I need xml format of output in the following format

<ROOT>

<Output >
< Departments>
<Department ID=“1" Name=“ Engineering ">
<Employees>
<Employee ID=“1" Name=“Senthil" >
<Salary>
<Month ID=“1" Salary=“ 50 "/>
<Month ID=“2" Salary=“ 60 "/>

<Month ID=“3" Salary=“ 60 "/>
<Month ID=“4" Salary=“ 70 "/>

<Month ID=“5" Salary=“ 50 "/>

<Month ID=“6" Salary=“ 60 "/>

</Salary>

</Employee>

<Employee ID=“2" Name=“Ram" >

<Salary>

<Month ID=“1" Salary=“ 55"/>

<Month ID=“2" Salary=“ 64 "/>

<Month ID=“3" Salary=“ 64 "/>

<Month ID=“4" Salary=“ 72 "/>

<Month ID=“5" Salary=“ 55 "/>

<Month ID=“6" Salary=“ 66 "/>

</Salary>
</Employee>
</Employees>
</Department >
</Departments>
</Output>
</ROOT>
*/"

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-30 : 12:38:26

I got you a good start with the following. I had a problem trying to get a separate entry for 'Employees', but perhaps the following will get you started and you can figure the rest out:
[CODE]
select 1 as Tag, NULL as Parent,
NULL as [Departments!1],
NULL as [Department!2!DeptId],
NULL as [Department!2!DeptName],
NULL as [Employee!3!EmployeeId],
NULL as [Employee!3!EmployeeName]
UNION ALL
select 2, 1, NULL, #dept.deptid, #dept.deptname, NULL, NULL
from #dept
UNION ALL
select 3, 2 , NULL, #dept.deptid, #dept.deptname, #emp.empid, #emp.empname
from #dept , #empdept , #emp
where #empdept.deptid = #dept.deptid
and #emp.empid = #empdept.empid
order by [Department!2!DeptId], [Employee!3!EmployeeId]
FOR XML EXPLICIT[/CODE]

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-30 : 13:06:56

I finished off the salary to, and pasted in part of the output, and pasted the output for the first department:
select 1 as Tag, NULL as Parent,
NULL as [Departments!1],
NULL as [Department!2!DeptId],
NULL as [Department!2!DeptName],
NULL as [Employee!3!EmployeeId],
NULL as [Employee!3!EmployeeName],
NULL as [Salary!4!Month],
NULL as [Salary!4!Pay]
UNION ALL
select 2, 1, NULL, #dept.deptid, #dept.deptname, NULL, NULL, NULL, NULL
from #dept
UNION ALL
select 3, 2 , NULL, #dept.deptid, #dept.deptname, #emp.empid, #emp.empname, NULL, NULL
from #dept , #empdept , #emp
where #empdept.deptid = #dept.deptid
and #emp.empid = #empdept.empid
UNION ALL
select 4, 3, NULL, #dept.deptid, #dept.deptname, #emp.empid, #emp.empname, #empsal.MonthId, #empsal.Salary
from #dept, #empdept, #emp, #empsal
where #empdept.deptid = #dept.deptid
and #emp.empid = #empdept.empid
and #empsal.empid = #emp.empid
order by [Department!2!DeptId], [Employee!3!EmployeeId], [Salary!4!Month]
FOR XML EXPLICIT

<Departments><Department DeptId="1" DeptName="Engineering"><Employee EmployeeId="1" EmployeeName="Senthil"><Salary Month="1" Pay="50.0000"/><Salary Month="2" Pay="60.0000"/><Salary Month="3" Pay="60.0000"/><Salary Month="4" Pay="70.0000"/><Salary Month="5" Pay="50.0000"/><Salary Month="6" Pay="60.0000"/></Employee><Employee EmployeeId="2" EmployeeName="Ram"><Salary Month="1" Pay="55.0000"/><Salary Month="2" Pay="64.0000"/><Salary Month="3" Pay="64.0000"/><Salary Month="4" Pay="72.0000"/><Salary Month="5" Pay="55.0000"/><Salary Month="6" Pay="66.0000"/></Employee></Department> .. next department...employee ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources