XML

Select * from GrantReviewComment
use MetroAlt
Create table Maintenance 
(
   MainenanceKey int identity(1,1) primary key,
   MaintenanceDate Date not null,
   Buskey int Foreign Key references Bus(Buskey)

)

Create table MaintenanceDetail
(
   MaintenanceDetailKey int identity (1,1) primary key,
   MaintenanceKey int foreign key references Maintenance(MainenanceKey),
   EmployeeKey int foreign key references Employee(EmployeeKey),
   BusServiceKey int,
   MaintainenceNotes nvarchar(255)
)

Create xml schema collection MaintenanceNotesSchema
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
elementFormDefault="qualified" 
targetNamespace="http://www.metroalt.com/maintenanceNotes" 
xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="maintenanceNotes">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="comments">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="comment" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="actions">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="action" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Alter table MaintenanceDetail
Drop column MaintainenceNotes

Alter table MaintenanceDetail
Add MaintenanceNotes xml (MaintenanceNotesSchema)

Insert into Maintenance(MaintenanceDate, Buskey)
Values(GetDate(), 3)

Insert into MaintenanceDetail(MaintenanceKey, EmployeeKey, BusServiceKey, MaintenanceNotes)
Values(Ident_current('Maintenance'), 69, 2,
'<?xml version="1.0" encoding="utf-8"?>
<maintenanceNotes xmlns="http://www.metroalt.com/maintenanceNotes">
  <comments>
    <comment>Bus stinks </comment>
 <comment>Broken back seat</comment>
  </comments>
  <actions>
    <action>Have bus cleaned and interior repaired</action>
  </actions>
</maintenanceNotes>')

Select * from MaintenanceDetail

Select Top 10 EmployeeLastName, EmployeeFirstName, EMployeeEmail  from Employee
for xml raw

Select Top 10 EmployeeLastName, EmployeeFirstName, EMployeeEmail  from Employee
for xml raw('employee'), elements, root('employees')

Select top 10 EmployeeLastName, EmployeeFirstName, EMployeeEmail, PositionName, EmployeeHourlyPayRate
From Employee
inner join EmployeePosition
on Employee.EmployeeKey=EmployeePosition.EmployeeKey
inner join Position
on Position.PositionKey=EmployeePosition.PositionKey
for xml auto, elements, root('employees')

Select MaintenanceKey, EmployeeKey, BusServiceKey, 
MaintenanceNotes.query('declare namespace mn="http://www.metroalt.com/maintenanceNotes";
//mn:maintenanceNotes/mn:comments/*') as comments from MaintenanceDetail