Monday, March 4, 2013

XML 

use Automart

Select * from Customer.AutoService

use CommunityAssist

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

Select lastName, FirstName, Contactinfo
From Person 
inner join PersonContact 
On person.PersonKey=personcontact.PersonKey
For xml auto, elements, root('people')

Create xml Schema Collection meetingNotesSchema
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="meetingNote">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="heading">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="meetingDate" type="xs:string" />
              <xs:element name="attending">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="member" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
              <xs:element name="subject" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="body">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="notes" type="xs:string" />
              <xs:element name="tasks">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="taskName" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table Meeting
(
 MeetingID int identity(1,1) Primary Key,
 MeetingNote xml(meetingNotesSchema)
)

Insert into Meeting(MeetingNote)
Values('<?xml version="1.0" encoding="utf-8"?>
<meetingNote xmlns="http://www.communityAssist.com/meetingNotes" >
  <heading>
    <meetingdate>3/4/2013</meetingdate>
    <attending>
      <member>George Jetson </member>
      <member>Mark Hammel</member>
      <member>Carie Fisher</member>
    </attending>
    <subject>Star Wars</subject>
  </heading>
  <body>
    <notes>
      We met to talk about our starwars promotion for something or other.
    </notes>
    <tasks>
      <taskName>Get the news out</taskName>
      <taskName>Mind meld</taskName>
    </tasks>
  </body>
</meetingNote>')


Select * from Meeting

use Automart

Select ServiceName, ServiceDescription.query('declare namespace sd="http://www.automart.com/servicedescription"; sd:servicedescription/sd:parts/sd:part') as parts
 from customer.AutoService
 Where ServiceName='Replace fuel pump'

 Select ServiceName, ServiceDescription.query('declare namespace sd="http://www.automart.com/servicedescription"; sd:servicedescription/sd:description') as [Description]
 from customer.AutoService
 Where ServiceName='Replace fuel pump'
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)