Admin XML and JSON assignments

--admin commands
Use MetroAlt
go
--schema management of objects
Create schema Management
Go
Create view Management.vw_AnnualRidership
As
Select year(BusscheduleAssignmentDate) [Year],
Count(riders) Total
From BusScheduleAssignment bs
inner join Ridership r
on r.BusScheduleAssigmentKey=bs.BusScheduleAssignmentKey
Group by year(BusscheduleAssignmentDate)

go
Create view Management.vw_EmployeeInfo
As
Select EmployeeFirstName, EmployeeLastName, EmployeeEmail,
PositionName, EmployeeHourlyPayRate
From Employee e
inner join EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner join Position p
on p.PositionKey=ep.PositionKey
Go
Create role managementRole

Grant Select on Schema::Management to ManagementRole

Grant Select, Insert, update on Employee to ManagementRole
Grant Select, Insert, Update on EmployeePosition to Managementrole

Create login managementLogin with password='P@ssw0rd1', default_database=MetroAlt
Use MetroAlt
Create user managementUser for login managementLogin 
Alter role managementrole add member managementUser

Backup Database MetroAlt to disk='C:\backup\MetroAlt.bak'
Restore Database metroAlt from disk='C:\backups\metroAlt'

--end admin assignment

--Begin Xml

Create xml Schema Collection MaintenanceNoteSchemaCollection
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
           elementFormDefault="qualified" 
           targetNamespace="http://www.metroalt.com/maintenancenote" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="maintenancenote">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="title" />
        <xs:element name="note">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="p" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="followup" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table MaintenanceDetail
(
     maintenanceDetailKey int identity(1,1) primary key,
  maintenanceNotes nvarchar(255)

)

Alter table MaintenanceDetail
Drop column MaintenanceNotes

Alter table MaintenanceDetail
add MaintenanceNotes xml (MaintenanceNoteSchemaCollection)

Insert into maintenanceDetail(maintenanceNotes)
values('<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <title>Wear and Tear on Hydralic units</title>
<note>
  <p>The hydralic units are showing signs of stress</p>
  <p>I recommend the replacement of the units</p>
</note>
  <followup>Schedule replacement for June 2016</followup>
</maintenancenote>')

--will fail because title is not Title
Insert into maintenanceDetail(maintenanceNotes)
values('<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <Title>Wear and Tear on Hydralic units</Title>
<note>
  <p>The hydralic units are showing signs of stress</p>
  <p>I recommend the replacement of the units</p>
</note>
  <followup>Schedule replacement for June 2016</followup>
</maintenancenote>')

Select * from MaintenanceDetail

--extras
Select * from Employee for xml raw('Employee'), elements, root('Employees')

--end xml
--begin JSON
Select * from Busbarn for JSON auto
Select BusBarnKey, Busbarnphone, busBarnAddress as 'address.street', 
Busbarncity as 'address.city', BusBarnZipcode as 'address.zipcode'
from Busbarn for JSON Path

Alter Database MetroAlt set compatibility_Level=130

Declare @JSON nvarchar(max)
Set @JSON='{"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13}'
Select * from OpenJSon(@JSON)

Declare @products nvarchar(max)
Set @products='[
    {"_id" : 1, "product": "IPad", "price" : 894.50, "quantityAvailable" : 13},
    {"_id" : 2, "product": "Chrome Book", "price" : 245.99, "quantityAvailable": 23},
    {"_id" : 3, "product": "Bose Lap Top Speakers", "price" : 89.50, "quantityAvailable" : 10},
    {"_id" : 4, "product": "Blue Tooth Game Controller", "price" : 149.99, "quantityAvailable" : 3},
    {"_id" : 5, "product": "Star Wars Mouse Pad", "price" : 1.50, "quantityAvailable" : 100},
    {"_id" : 6, "product": "Dell XPS Desk Top Computer", "price" : 945.00, "quantityAvailable" : 7},
    {"_id" : 7, "product": "Microsoft Surface Pro", "price" : 1250.75, "quantityAvailable" : 9},
    {"_id" : 8, "product": "Norton Anti Virus", "price" : 75.50, "quantityAvailable": 2},
    {"_id" : 9, "product": "Mechanical Keyboard", "price" : 125.50, "quantityAvailable" : 3},
    {"_id" : 10, "product": "Android Tablet", "price" : 345.23, "quantityAvailable" : 5}
  ]'
  Select * from OpenJson(@products)
  with (
    id int '$._id',
 product nvarchar(255) '$.product',
 price decimal(8,2) '$.price',
 quantity int '$.quantityAvailable'
 )
--end JSON