Thursday, March 10, 2016

XML in SQL Server 

--xml
use CommunityAssist

--raw is the simplest way to take tabular data and turn it into XML
--below are variations of the raw
Select PersonLastName, PersonFirstName,
PersonUserName
 From Person
For xml raw

Select PersonLastName, PersonFirstName,
PersonUserName
 From Person
For xml raw, elements 

Select PersonLastName, PersonFirstName,
PersonUserName
 From Person
For xml raw, elements, root('people')



Select PersonLastName, PersonFirstName,
PersonUserName
 From Person
For xml raw('person'), elements, root('people')

--xml auto lets you create deeper Hierachies
--the query below groups all the grants of the same
--kind together
Select ServiceName, GrantKey, GrantNeedExplanation, GrantAmount
From CommunityService [Service]
inner Join ServiceGrant Grants
on [Service].ServiceKey = Grants.ServiceKey
order by Grants.ServiceKey
For XML auto, elements, root('root')

Select * From GrantReview

--this is a very simple example of XQuery, a language
--you can use to query XML documents
Select GrantReviewDate, GrantKey, 
GrantReviewNote.query('declare namespace mn="http://www.communityassist.org/reviewnotes"; 
//mn:reviewnote/mn:comment') as Attending
From GrantReview
Where GrantKey =40

--a schema collection stores an XML Schema.
--an xml schema is an xml document that describes
--the structure of another xml document
--a schema collection can be used to enforce the 
--structure of xml documents stored in the database
Create xml Schema Collection DonorNotesSchemaCollection
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.communityassist.org/donornote" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="donornote">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="heading">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="title" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="body">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="note" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

--creating a table with an xml column
--the xml columns is set to be validated by the schema
--in our schema collection. This insures that it has
--a consistent structure
Create table DonorReview
(
    DonorReviewKey int identity(1,1) primary key,
 DonationKey int foreign key references Donation(DonationKey),
 DonorReviewDate Date Default GetDate(),
 DonorNote xml(DonorNotesSchemaCollection)
)

Insert into DonorReview(DonationKey, DonorNote)
Values(4
, '<?xml version="1.0" encoding="utf-8"?>
<donornote xmlns="http://www.communityassist.org/donornote">
  <heading>
    <title>Big Donor</title>
  </heading>
  <body>
  <note>
    This donor just gave a huge donation
  </note>
  <note>
    We should spam them for more
  </note>
    </body>
</donornote>')

Select * from DonorReview
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)