SQL and XML

use CommunityAssist

Select PersonFirstName, PersonLastName, PersonUsername 
From Person for xml raw('person'), root('people'),  elements

Select ServiceName, GrantKey, GrantAmount, GrantDate, GrantNeedExplanation
From CommunityService 
inner join ServiceGrant 
on CommunityService.ServiceKey=ServiceGrant.ServiceKey
order by Servicename
for xml auto,  root('services'), elements

Select GrantReviewDate, GrantReviewNote.query( 'declare namespace rn = "http://www.communityassist.org/reviewnotes"; //rn:reviewnote/rn:concerns')
From GrantReview 


Create xml Schema Collection schemaMeeting
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.CommunityAssist.org/meeting" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="meeting">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="attending">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="attendee" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="topic" type="xs:string" />
        <xs:element name="notes" type="xs:string" />
        <xs:element name="tasks">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="task" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table Meeting
(
    MeetingKey int identity(1,1) primary key,
 MeetingDate date not null default GetDate(),
 MeetingNotes xml(schemaMeeting)
)

Insert into Meeting(MeetingNotes)
Values('<?xml version="1.0" encoding="utf-8"?>
<meeting xmlns="http://www.CommunityAssist.org/meeting">
  <attending>
    <attendee>Conger</attendee>
    <attendee>Lisa</attendee>
    <attendee>Ed</attendee>
  </attending>
  <topic>new computers</topic>
  <notes>
    New computers will be coming next quarter
  </notes>
  <tasks>
    <task>Create install plan</task>
    <task>something</task>
  </tasks>
</meeting>')

Select * From Meeting