XML

here is the xml file we made: memo.xml

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="memo.xslt"?>
<m:memo xmlns:m="http://www.MetroAlt.com/memo">
 <m:heading>
  <m:to>Drivers</m:to>
  <m:from>Dispatchers</m:from>
  <m:about>Road Closures</m:about>
  <m:date>2015-07-28</m:date>
 </m:heading>
 <m:body>
  <m:p>There are several road closures this week.</m:p>
  <m:p>Watch for bulletons and notices.</m:p>
 </m:body>
</m:memo>

here is the schema that describes the xml document: memo.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <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:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

Here is the xslt file: memo.xslt

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <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:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

Here are the files we did for SQL Server

Create xml Schema collection sch_memo
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <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:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>'

Create table memos
(
   memoId int identity(1,1),
   memoText xml(sch_memo)
)

Insert into memos (memoText)
Values('<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="memo.xslt"?>
<m:memo xmlns:m="http://www.MetroAlt.com/memo">
 <m:heading>
  <m:from>Dispatchers</m:from>
  <m:to>Drivers</m:to>
  <m:about>Road Closures</m:about>
  <m:date>2015-07-28</m:date>
 </m:heading>
 <m:body>
  <m:p>There are several road closures this week.</m:p>
  <m:p>Watch for bulletons and notices.</m:p>
 </m:body>
</m:memo>')

Select * from Employee 
Where EmployeeCity='Kent'
For xml raw('employee'), elements, root('employees')


Select PositionName, Employeelastname, EmployeeFirstName, EmployeeEmail
From Position
inner join EmployeePosition
on Position.PositionKey=EmployeePosition.PositionKey
inner Join Employee
on Employee.EmployeeKey=EmployeePosition.EmployeeKey
Where EmployeeCity='Bellevue'
order by PositionName
for xml auto, elements, root('Employees')

use CommunityAssist
Select GrantReviewdate, EmployeeKey, GrantKey, 
GrantReviewNote.query('declare namespace rn = "http://www.communityassist.org/reviewnotes";//rn:reviewnote/rn:recommendation') as recommendations
from GrantReview