XML Lab

Enter your name

XML as a Native Data type

Copy the following script to a new Query window in SQL Server
Run it to add a table to Automart and insert three records with the xml data type

Use Automart
Go
Create table Memo
(
	memoid int identity(1,1) primary key,
	memoXml xml
)
go
insert into Memo(memoXML)
Values( '<memo xmlns="www.automart.com/memos">
<head>
<date>5/21/2007</date>
<to>Leah Johnson</to>
<from>Corporate Headquarters</from>
<re>Holiday Special</re>
</head>
<body>
<para>We are encouraging all our locations
to participate in our memorial day special and
to provide a 20 percent discount on all
services </para>
<para>Thank you</para>
</body>
</memo>' )

insert into Memo(memoXML)
Values( '<memo xmlns="www.automart.com/memos">
<head>
<date>6/15/2007</date>
<to>Carol Manning</to>
<from>Corporate Headquarters</from>
<re>Customer Complaint</re>
</head>
<body>
<para>We have recieved a complaint from one of your
customers, who says a mechanic was rude to her
when she asked him a question. </para>
<para>We realize this is an isolated incidence, however
we encourage you to review customer relations with all
your staff</para>
<para>Thank you</para>
</body>
</memo>' )

insert into Memo(memoXML)
Values( '<memo xmlns="www.automart.com/memos">
<head>
<date>4/10/2007</date>
<to>Laura Lovbe</to>
<from>Corporate Headquarters</from>
<re>Congratulations</re>
</head>
<body>
<para>You have been voted our manager of the month.
We will be sending you a plaque for your wall soon</para>
<para>Thank you</para>
</body>
</memo>' )

Run "SELECT * FROM Memo

How is the xml displayed

Simple XQUERY

Run this query

Select memoXML.query('declare namespace mm="www.automart.com/memos";         
    /mm:memo/mm:head/mm:date') as Result
From Memo

What were the results?

Now run this XQuery code

Select memoXML.query('declare namespace mm="www.automart.com/memos";         
    /mm:memo/mm:head/mm:re') as Result
From Memo 
where memoID=2

What were the results?

Now run this third xQuery

Select memoXML.query('declare namespace mm="www.automart.com/memos";         
    /mm:memo/mm:body/mm:para') as Result
From Memo
where memoid=1

What are the results

For XML

Run this query

Select CLastName, CFirstName, CEmail 
from Customer
For XML auto

What did you get

Now we will use xml Raw
Do the same query but substitute "raw" for "Auto"

What did you get?

Add aa comma and "elements to the end of the last query
Run it.

What is different?

Modify the query so it looks like this:

	
Select CLastName, CFirstName, CEmail 
from Customer
For XML raw, root('customerRoot'),elements

What do you get for a root element? What do you have for a row grouping element?

Modify the query one more time to look like this:

Select CLastName, CFirstName, CEmail 
from Customer
For XML raw('customer'), root('customerRoot'),elements

What is different from the last query results?

Lab: XML

XML as a Native Data type

Simple XQUERY

For XML