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

NoSQL Collections and JSON

Showtracker  --Track shows venues artists reviews
Bookreviews --Books Reviewers authors Genre Reviews
JSON
bookCollection
[
{
  _id : 1,
  title : "The Philosopher's Stone",
  releaseDate : "1997",
  isbn : "54546525775",
  author : "J.K. Rowling",
  genre : ["fantasy", "young adult"],
  description : "fantasy about a boy who finds out he is actually a wizard."
},
{
   _id : 2,
   title : "Nebula Award Winners 2017",
   releaseDate : "2017",
   isbn: "349834943290",
   author: [
             "Neil Gaimen",
             "Ben Bova",
             "Wesley Chu",
             "William Gibson"
            ],
   genre : "Science Fiction",
   description: "A collection of award winning SF stories"
}
]

authorCollection

  {
    _id : 1,
    authorName : "J.K. Rowling",
    country : "England",
    books : [
              {_id : 1, title : "The Philosopher's Stone", releaseDate : "1997"},
              {_id : 3, title : "The Chamber of Secrets", releaseDate : "1999"},
              {_id : 4, title : "Prisoner of Askaban", releaseDate : "2000"},
              {_id : 5, title : "The Casual Vacancies", releaseDate : "2010"}
      ]
   }

reviewerCollection
{
  _id : 1,
  name : "Steve Conger",
  email : "spconger@gmail.com"
  
}

reviewCollection
{
   _id : 1,
   bookId : 1,
   bookTitle : "The Philosopher's Stone",
   bookAuthor : "J. K. Rowling",
   reviewDate: "3/6/2017",
   reviewAuthor : {_id : 1, name: "Steve Conger"},
   rating : "4",
   title : "the beginning of a long series",
   review : " fdsjldfsjljfl;dsjfds;ljsda"
}