Monday, May 21, 2012

XML and SQL 

use CommunityAssist

--xml
--outputting data as xml
--xml as a data type
--xml schema collections
--xquery
--these are using all options
Select * from Person for xml raw('person'), root ('Persons'), elements

Select lastName, FirstName, DonationDate, donationAmount
From Person 
Inner Join Donation 
on person.PersonKey=donation.PersonKey
for xml auto, elements, root('dataroot') 

--explicit: you have to specify exactly where 
in the xml heirarchy each field belongs

--xml as a datatype

Select * From Meeting

--we are dropping the column in order to add it back in with a 
--schema

Alter Table Meeting
drop column meetingnotes

--this creates a schema collection which can be used
--to test whether a given xml document entered
--into an xml data column conforms to certain specifications
Create xml schema collection MeetingNotesSchemaCollection
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
elementFormDefault="qualified" 
targetNamespace="http://www.seattlecentral.edu/meetingnotes" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="meetingnotes">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="heading">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="attending">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="member" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="body">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="notes" />
              <xs:element name="tasks">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="taskname" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

--add back in the table column with the schema
Alter table meeting
Add meetingnotes xml(MeetingNotesSchemaCollection)

--insert a record with the xml column
Insert into Meeting (MeetingDate, MeetingTopic, meetingnotes)
Values(GETDATE(), 'More Naps',
'<?xml version="1.0" encoding="utf-8"?>
<meetingnotes xmlns="http://www.seattlecentral.edu/meetingnotes" >
  <heading>
    <attending>
      <member>Joe</member>
      <member>Sue</member>
    </attending>
  </heading>
  <body>
    <notes>The staff would like more nap time</notes>
    <tasks>
      <taskname>Find a napping place</taskname>
      <taskname>find a nap time</taskname>
    </tasks>
  </body>
</meetingnotes>')

select * from meeting


--this one fails because the capital A on Attending doesn't
--conform to the schema
Insert into Meeting (MeetingDate, MeetingTopic, meetingnotes)
Values(GETDATE(), 'More Naps',
'<?xml version="1.0" encoding="utf-8"?>
<meetingnotes xmlns="http://www.seattlecentral.edu/meetingnotes" >
  <heading>
    <Attending>
      <member>Joe</member>
      <member>Sue</member>
    </Attending>
  </heading>
  <body>
    <notes>The staff would like more nap time</notes>
    <tasks>
      <taskname>Find a napping place</taskname>
      <taskname>find a nap time</taskname>
    </tasks>
  </body>
</meetingnotes>')

--another insert
Insert into Meeting (MeetingDate, MeetingTopic, meetingnotes)
Values(GETDATE(), 'Application Review',
'<?xml version="1.0" encoding="utf-8"?>
<meetingnotes xmlns="http://www.seattlecentral.edu/meetingnotes" >
  <heading>
    <attending>
      <member>Bob</member>
      <member>Lisa</member>
      <member>Sasha</member>
    </attending>
  </heading>
  <body>
    <notes>We reviewed 20 applications and approved 12</notes>
    <tasks>
      <taskname>Inform recipients</taskname>
     
    </tasks>
  </body>
</meetingnotes>')

--these are examples of xquery
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 MeetingDate, MeetingTopic, 
meetingnotes.query('declare namespace mn="http://www.seattlecentral.edu/meetingnotes"; 
//mn:meetingnotes/mn:body/mn:notes') as Attending
From Meeting

Select MeetingDate, MeetingTopic, 
meetingnotes.query('declare namespace mn="http://www.seattlecentral.edu/meetingnotes";
 //mn:meetingnotes/mn:body') as Attending
From Meeting
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)