SQL Server and XML [modified]
-
Maybe it's just me, but I can't stand how Microsoft implemented XML in SQL Server, syntax wise, I mean, when it comes to selecting table data to XML. I always thought something like this made much more sense:
Select
Convert(xml, '<?xml version="1.0" encoding="utf-8" ?>
<Employees>
#foreach emp in emp begin#
<Employee id="emp.EmployeeID#">
<Manager id="#emp.BossID#">#emp.BossName#</Manager>
<Addresses>
#foreach addr in emp.Address1 begin#
<Address id="#addr.AddressID#">#addr.Address1 + " " + addr.City + ", " + addr.State + " " addr.ZipCode#</Address>
#end#
</Addresses>
</Employee>
#end#
</Employees>
') As EmployeeDoc
From
(
Select
emp.ID As EmployeeID
,emp.Name As EmployeeName
,boss.ID As BossID
,boss.Name As BossName
,addr.ID As AddressID
,addr.Address1
,addr.City
,addr.State
,addr.ZipCode
From
Employees emp
Left Join Employees boss
On emp.ManagerID = emp.ID
Inner Join EmployeeAddresses empaddr
On emp.ID = empaddr.EmployeeID
Inner Join Addresses addr
On empaddr.AddressID = addr.ID
Where
emp.TerminationDate Is Null
) tbl;I'm still working on learning the current syntax, so maybe this is a bit of a soapbox, but I feel better now anyways. :)
Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA
modified on Monday, October 20, 2008 3:32 PM