Blog Pages

FOR XML EXPLICIT

SELECT 1            AS Tag,
       NULL         AS Parent,
       e.EmployeeID AS [Employee!1!EmployeeID],
       c.Name  AS [Employee!1!Name],
       NULL         AS [Address!2!AddressLine1],
       NULL         AS [Address!2!PostalCode]
 FROM  HumanResources.Employee e(NOLOCK)
 JOIN  Person.Contact c (NOLOCK)
   ON  e.ContactID = c.ContactID
UNION ALL
SELECT 2            AS Tag,
       1            AS Parent,
       e.EmployeeID AS [Employee!1!EmployeeID],
       NULL         AS [Employee!1!Name],
       AddressLine1 AS [Address!2!AddressLine1],
       PostalCode   AS [Address!2!PostalCode]
 FROM  HumanResources.Employee e(NOLOCK)
 JOIN  HumanResources.EmployeeAddress ea(NOLOCK)
   ON  e.EmployeeID = ea.EmployeeID
 JOIN  Person.Address a (NOLOCK)
   ON  ea.AddressID = a.AddressID  
 JOIN  Person.StateProvince s (NOLOCK)
   ON  a.StateProvinceID = s.StateProvinceID
ORDER BY e.EmployeeID, Tag
FOR XML EXPLICIT, ROOT('Employees')

Result:
<Employees>
  <Employee EmployeeID="1" Name="Guy" >
    <Address AddressLine1="7726 Driftwood Drive" PostalCode="98272" />
  </Employee>
  <Employee EmployeeID="2" Name="Kevin" >
    <Address AddressLine1="7883 Missing Canyon Court" PostalCode="98201" />
  </Employee>
</Employees>

No comments:

Post a Comment