Admin
--authentication
--Authorization
Use Master
Create Login EmployeeLogin with password='P@ssw0rd1', default_database=communityAssist
Use CommunityAssist
Create user EmployeeUser for Login EmployeeLogin
Create role EmployeeRole
Grant Select, Update on ServiceGrant to EmployeeRole
Grant Insert, Select, Update on GrantReview to EmployeeRole
Grant select on person to Employeerole
exec sp_addrolemember 'EmployeeRole', 'EmployeeUser'
XML
/* way to transfer and carry data
an element <element> </element> <element/>
unicode
nested properly
attributes
root element -- namespaces
<memo xmlns="http://spconger.com/memo">
Schema: xsd:
xslt */
use CommunityAssist
Select PersonLastname, PersonfirstName,
PersonUserName from Person for xml raw('person'), elements, root('people')
Select PersonlastName, PersonfirstName,
EmployeeHireDate, EmployeeStatus, EmployeeMonthlySalary
From Person
inner join Employee
on person.PersonKey=employee.PersonKey
for xml auto, elements, root('Employees')
Select * From GrantReview
Create xml Schema Collection meetingSchema
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.communityassist.com/meetingnotes" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="meetingnotes">
<xs:complexType>
<xs:sequence>
<xs:element name="head">
<xs:complexType>
<xs:sequence>
<xs:element name="date" type="xs:string" />
<xs:element name="topic" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="body">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="p" type="xs:string" />
<xs:element name="actions">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="item">
<xs:complexType>
<xs:attribute name="name" type="xs:string" use="required" />
<xs:attribute name="due" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
Create table Meeting
(
MeetingKey int identity primary key,
MeetingDate Date not null,
MeetingNotes xml(meetingSchema)
)
Insert into Meeting (MeetingDate, MeetingNotes)
Values(GetDate(),
'<?xml version="1.0" encoding="utf-8"?>
<meetingnotes xmlns="http://www.communityassist.com/meetingnotes">
<head>
<date>3/5/2014</date>
<topic>Xml</topic>
</head>
<body>
<para>
Xml is very useful. Very simple in concept.
</para>
<para>
But it can get complicated in practice
</para>
<actions>
<item name="Create Schema" due="3/5/2014"/>
<item name="Query" due="3/5/2014"/>
</actions>
</body>
</meetingnotes>')
select * from meeting
Select * from GrantReview
/*Select MeetingDate, MeetingTopic,
meetingnotes.query('declare namespace mn="http://www.seattlecentral.edu/meetingnotes";
//mn:meetingnotes/mn:heading/mn:attending/mn:member') as Attending
From Meeting*/
Select GrantReviewDate, EmployeeKey, GrantReviewNote.query
('declare namespace rv="http://www.communityassist.org/reviewnotes"; //rv:reviewnote/rv:comment')
From GrantReview
Where GrantReviewDate='8/11/2013'
No comments:
Post a Comment