Thursday, March 8, 2018

XML

Use MetroAlt
Select top 10 EmployeeLastName, EmployeeFirstName, EmployeeEmail
From Employee 
For XML raw

Select top 10 EmployeeLastName, EmployeeFirstName, EmployeeEmail
From Employee 
For XML raw, root('employee'), elements

Select BusbarnAddress, BusBarnCity, Bus.Buskey
From BusBarn
Inner Join Bus
On Bus.BusBarnKey=BusBarn.BusBarnKey
For XML auto, root('Barn'), elements

Create table Maintenance(
   maintenanceKey int identity primary key,
   maintenanceDate Date default getdate(),
   maintenanceNotes Nvarchar(255)
)

Alter table Maintenance
drop column MaintenanceNotes

Create xml schema collection xmlSchema_notes
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.metroalt.com/maintenancenote" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="maintenancenote">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="title" type="xs:string" />
        <xs:element name="note">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="followup" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Alter table Maintenance
Add MaintenanceNotes xml (xmlschema_notes)

insert into Maintenance([MaintenanceNotes])
values('<?xml version="1.0" encoding="utf-8"?>
<maintenancenote xmlns="http://www.metroalt.com/maintenancenote">
  <title>Wear and Tear on Hydralic units</title>
<note>
  <p>The hydralic units are showing signs of stress</p>
  <p>I recommend the replacement of the units</p>
</note>
  <followup>Schedule replacement for June 2016</followup>
</maintenancenote>')

Select * from Maintenance

Select MaintenanceKey, MaintenanceDate, 
maintenanceNotes.query
('declare namespace mn="http://www.metroalt.com/maintenancenote";//mn:maintenancenote/mn:title') titles
From Maintenance

No comments:

Post a Comment