/
XML
44 lines (38 loc) · 1.3 KB
/
XML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
--XML assignment
use MetroAlt
Create xml schema collection managementNotesXMLschema
as
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.metroalt.com/notes" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="maintenancenotes">
<xs:complexType>
<xs:sequence>
<xs:element name="title" type="xs:string" />
<xs:element maxOccurs="unbounded" name="note" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
create table MaintenanceNotes
(
maintenanceNotesKey int identity(1,1),
maintenanceNotesDate Date,
maintenanceNote xml (managementNotesXMLschema)
)
insert into MaintenanceNotes
(maintenanceNotesDate, maintenanceNote)
values
(GetDate(), '<?xml version="1.0" encoding="utf-8"?>
<maintenancenotes xmlns="http://www.metroalt.com/notes">
<title>Bus Service 24</title>
<note>The brakes are shot</note>
<note>Needs new tires</note>
</maintenancenotes>')
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