FOR XML AUTO - 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 AUTO

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-05-19 : 12:55:04

Hi,

I have this SQL.

SELECT Dept.[DepartmentID]
,History.[EmployeeID]
,History.[StartDate]
,Dept.[Name] AS DepartmentName
,DATEDIFF(year, History.[StartDate], GetDate()) AS YearsToDate
FROM HumanResources.Department Dept, HumanResources.EmployeeDepartmentHistory History
WHERE Dept.DepartmentID = History.DepartmentID
AND History.EndDate IS NULL
ORDER BY Dept.[DepartmentID], History.[StartDate]
FOR XML AUTO, ELEMENTS

Section of the dataset comes out of the this XML in table format is this.

http://img338.imageshack.us/img338/8388/datac.png

When I get the real XML output,

<Dept>
<DepartmentID>1</DepartmentID>
<DepartmentName>Engineering</DepartmentName>
<History>
<EmployeeID>3</EmployeeID>
<StartDate>1997-12-12T00:00:00</StartDate>
<YearsToDate>13</YearsToDate>
</History>
<History>
<EmployeeID>9</EmployeeID>
<StartDate>1998-02-06T00:00:00</StartDate>
<YearsToDate>12</YearsToDate>
</History>
<History>
<EmployeeID>11</EmployeeID>
<StartDate>1998-02-24T00:00:00</StartDate>
<YearsToDate>12</YearsToDate>
</History>
<History>
<EmployeeID>12</EmployeeID>
<StartDate>1998-03-03T00:00:00</StartDate>
<YearsToDate>12</YearsToDate>
</History>
<History>
<EmployeeID>267</EmployeeID>
<StartDate>2001-01-30T00:00:00</StartDate>
<YearsToDate>9</YearsToDate>
</History>
<History>
<EmployeeID>270</EmployeeID>
<StartDate>2001-02-18T00:00:00</StartDate>
<YearsToDate>9</YearsToDate>
</History>
</Dept>

This is great.

But I am wondering ..

2. Why did it do

<DepartmentID>1</DepartmentID>
<DepartmentName>Engineering</DepartmentName>

Instead of
<dept>
<DepartmentID>1</DepartmentID>
<DepartmentName>Engineering</DepartmentName>
<dept>
<History>
....
</History>

is it becasue of the order by clause I have in the SQL ?
Because if I remove it, it does not add all the employees in the same Dept element, but still it does not add the <dept> element on top of the department elements.

Any Ideas?

Please let me know when you have time.

Thanks,>
Shiyam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources