ITC Resources: XML in SQL Server


--xml

use CommunityAssist

Select * from person for xml raw

Select * From person for xml raw, elements, root('People')

Select * From person for xml raw('person'), elements, root('people')

Select lastName, firstname, DonationDate, donationAmount
From Person 
inner Join Donation
on person.PersonKey=donation.PersonKey
for xml auto

Select lastName, firstname, DonationDate, donationAmount
From Person 
inner Join Donation
on person.PersonKey=donation.PersonKey
for xml auto, elements, root('root');

use Automart

Select * From Customer.AutoService
Select AutoServiceID, serviceName, serviceprice, serviceDescription.query('declare namespace sd="http://www.automart.com/servicedescription";//sd:servicedescription/sd:parts')
From Customer.AutoService
Where AutoServiceID=7

use CommunityAssist

--an xml schema "validates" an xml document
--by comparing its structure to the one described
--in the schema
--a schema collection stores the schema in sql server
--for comparing xml documents
Create xml Schema Collection xsc_Memo
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="memo">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="head">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="to" type="xs:string" />
              <xs:element name="from" type="xs:string" />
              <xs:element name="about" type="xs:string" />
              <xs:element name="date" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="body">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="para" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="namespace" type="xs:string" use="required" />
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table Meeting
(
 MeetingID int identity(1,1) primary key,
 MeetingNotes xml(xsc_Memo) --this binds the xml to the schema
)


Insert into Meeting (MeetingNotes)
Values('<?xml version="1.0" encoding="utf-8"?>
<memo namespace="http://www.communityassist.org/memo">
  <head>
    <to>all staff</to>
    <from>Management</from>
    <about>Work behavior</about>
    <date>5/20/2013</date>
  </head>
  <body>
    <para>It has come to our attention that employees are starting the after work beer drinking earlier each friday</para>
    <para>Stop it.</para>
  </body>
</memo>')

--this one won't work because the to and from are reversed in order
Insert into Meeting (MeetingNotes)
Values('<?xml version="1.0" encoding="utf-8"?>
<memo namespace="http://www.communityassist.org/memo">
  <head>
    <from>Management</from>
 <to>all staff</to>
    <about>Work behavior</about>
    <date>5/20/2013</date>
  </head>
  <body>
    <para>It has come to our attention that employees are starting the after work beer drinking earlier each friday</para>
    <para>Stop it.</para>
  </body>
</memo>')

Select * From Meeting
ITC Resources

Monday, May 20, 2013

XML in SQL Server

No comments:

Post a Comment

About Me

Blog Archive
