Admin and XML

--the Amin Assignment

use metroalt

--what is a schema ownership of object
--default dbo.
go
Create schema ManagementSchema


Go
Create view ManagementSchema.vw_AnnualRidership
As
Select Year(BusScheduleAssignmentDate) as [Year],
Sum(Riders) as Total
From BusScheduleAssignment bsa
join Ridership r
on bsa.BusScheduleAssignmentKey=r.BusScheduleAssigmentKey
Group by Year(BusScheduleAssignmentDate)
go

Create role ManagementRole

Grant Select, insert, update on Employee to ManagementRole

Grant select on Schema::managementSchema to managementRole

Create login Albert with password='P@ssw0rd1'

Create user Albert for login Albert



exec sp_addrolemember 'ManagementRole','Albert'

Backup Database MetroAlt To Disk='C:\Backup\MetroAlt.bak'

--XML Assignment
Create Xml Schema Collection ManagementNotesXMLSchema
As
'

  
    
      
        
        
      
    
  
'

Create Table maintenanceNotes
(
   maintenanceNotesKey int identity(1,1),
   maintenanceNotesDate Date,
   maintenanceNote xml (managementNotesXmlSchema)

)

Insert into maintenanceNotes (maintenanceNotesDate, maintenanceNote)
Values (GetDate(), '

  
  
  
    The brakes are shot
  
  
    Needs new tires
  
')


Select * from maintenanceNotes

Use Community_Assist

Select * From GrantReviewComment

Select GrantReviewKey, EmployeeKey, 
GrantReviewCommentDate, 
GrantReviewNote.query
('declare namespace rn="http://www.community_assist.org/reviewnotes";//rn:reviewnote/rn:recommendation')
From GrantReviewComment