XML AJAX and ADO.Net

XML AJAX andADO.Net

XML: the big picture • Be wary… • _______ changes EVERYTHING! • Or _______ is a Paradigm Shift! • For the last few years, technology pundits have XML in the blank. • Microsoft is pushing XAML as part of the next version of Windows OS • Extensible Application Markup Language • http://msdn2.microsoft.com/en-us/library/ms752059.aspx

AJAX: the big picture • Be wary… • _______ changes EVERYTHING! • Or _______ is a Paradigm Shift! • For the last few years, technology pundits have AJAX in the blank. • The main purpose of AJAX appears to be to bring web applications closer to the richness of interaction available to desktop applications.

AJAX: the big picture • The primary “trick” which pulls this off is an asynchronous connection with the web server • Remember, the web is a stateless environment (or at least the protocols are…) • This separate communication line allows for “partial” updates and data transfer

AJAX: the big picture • Partial updates require: • A means of identifying parts of a page or more specifically a document: DOM • A standard way of transferring data to and from a data source which is compatible with this model: XML • A standard means of marking up these documents: XSL • Let’s look at the XML portion first.

XML: the big picture • Two world views: documents/data • Documents: semi-structured • Data: very structured (think ERD) • The catalyst for convergence • The Internet and WWW • XML: a data exchange format • Pundits have called this encounter a “technology train wreck”

XML: the big picture • The WWW defined a simple and universal standard for document exchange (HTML). • Information is decomposed into named units, marked up, and transmitted • URL • HTML • HTTP

XML: the big picture • The content of these named units of marked up information comes from somewhere… • Databases! • Generating documents is most often solved using a three-tier architecture.

XML: the big picture • As an standard, HTML works well for publication, but not so well for data exchange. • Exchange requires screen scraping and HTML parsing. • http://en.wikipedia.org/wiki/Screen_scraping • Not a robust solution • XML provides a bridge between systems (data exchange format)

Two cultures collide • Web developers (Documents view) • Global infrastructure • Standards for document retrieval • Standards for materializing documents • Semi-structured data

Two cultures collide • Database view • Storage techniques • Standard query language • Efficient access to large collections of highly structured data • Data models for structuring data • Mechanisms for maintaining data integrity and consistency

XML: the basics • A caveat: XML is a VERY large (and often confusing) collection of emerging standards. • Our focus is on the convergence, that is, the relation of XML to databases.

Semi-structured Data • HTML describes how to present or render the content of an HTML document, that is the structure of the document. • For example

Semi-structured Data <HTML> <BODY> <TABLE BORDER=1> <TR><TD>1</TD><TD>Davolio</TD><TD>Nancy</TD></TR> <TR><TD>1</TD><TD>Fuller</TD><TD>Andrew</TD></TR> <TR><TD>1</TD><TD>Leverling</TD><TD>Janet</TD></TR> </TABLE> </BODY> </HTML>

Semi-structured Data Although both are human-readable (more-or-less), there is nothing to assist a software program in identifying the structure of the data. <HTML> <BODY> <TABLE BORDER=1> <TR><TD>1</TD><TD>Davolio</TD><TD>Nancy</TD></TR> <TR><TD>1</TD><TD>Fuller</TD><TD>Andrew</TD></TR> <TR><TD>1</TD><TD>Leverling</TD><TD>Janet</TD></TR> </TABLE> </BODY> </HTML>

Semi-structured Data • HTML describes the structure of the document, but what is needed is the structure of the data. • XML separates • Document structure (DTD) • Content (Elements, Attributes) • Materialization (XSL) • In HTML, these are confounded

Semi-structured Data • This confounding doesn’t matter for rendering and printing • A problem arises when developers try to use these rendered documents to electronically exchange data • A natural extension of the analog world really

XML • Moving from semi-structured data to structured data and back again requires an abstraction of what a document contains • For example, online resume system for Career Services • Abstract but specific to domain • But abstraction isn’t enough • Documents must also follow rules

XML • The idea of XML is that a document is defined by it’s structure, not by it’s formatting • Formatting is specific to application or interface • Document structure defines the data

XML: Basic Syntax • Elements • A piece of text bounded by matching tags • <employee>Harold</employee> • <element>content</element> • Elements (tags) can be anything • Elements can contain elements as well as content

XML: Basic Syntax • Sub-elements <EMPLOYEES> <EMPLOYEEID>1</EMPLOYEEID> <LASTNAME>Davolio</LASTNAME> <FIRSTNAME>Nancy</FIRSTNAME> </EMPLOYEES> EmployeeID, LastName, and FirstName are sub-elements of Employee

XML: Basic Syntax • Sub-elements • Describe the relation between an element and its component elements • An Employee has an id, last name, and first name. • The query analyzer in SQL Server will create this structure from a query

XML: Basic Syntax Set nocount on; SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES FOR XML AUTO, ELEMENTS I’ll discuss this syntax later.

XML: Basic Syntax

<HTML> <BODY> <TABLE BORDER=1> <TR><TD>1</TD><TD>Davolio</TD><TD>Nancy</TD></TR> <TR><TD>1</TD><TD>Fuller</TD><TD>Andrew</TD></TR> <TR><TD>1</TD><TD>Leverling</TD><TD>Janet</TD></TR> </TABLE> </BODY> </HTML> Although the HTML and XML documents contain the same content, the HTML document describes how to present the data. The XML document describes the structure of the data.

XML: Basic Syntax • Attributes • XML allows the developer to associate name/value pairs with an element. • Attributes can contain metadata about the element or the content itself. • The current thinking seems to be that elements should contain content and attributes should contain metadata. • Page 457

SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES FOR XML RAW Using the RAW option, the XML content can be represented as attributes.

In this case, I’ve include the data type of the element. Go to http://www.w3.org/2001/XMLSchema.xsd for the current standards.

XML: Basic Syntax • If the tags are balanced (match) and the attributes are unique the XML document is said to be well-formed. • However, all this constraint ensures is that the XML document will parse into a labeled tree. • Is that sufficient for data exchange? • No. • But first, let’s look at some more basic structure.

XML: Basic Syntax • The preceding example came from just one table: Employees. • Most transactions will involve multiple tables. • How are multiple tables represented? • A tree • A graph

SELECT E.EMPLOYEEID, LASTNAME, ORDERID, CUSTOMERID, SHIPCITY, ORDERDATE FROM EMPLOYEES E, ORDERS O WHERE E.EMPLOYEEID = O.EMPLOYEEID AND ORDERDATE BETWEEN '1997-01-01' AND '1997-01-02' FOR XML AUTO, ELEMENTS

SELECT E.EMPLOYEEID, LASTNAME, ORDERID, C.CUSTOMERID, SHIPCITY, COMPANYNAME FROM EMPLOYEES E, ORDERS O, CUSTOMERS C WHERE E.EMPLOYEEID = O.EMPLOYEEID AND C.CUSTOMERID=O.CUSTOMERID AND ORDERDATE BETWEEN '1997-01-01' AND '1997-01-02' FOR XML AUTO, ELEMENTS

XML • Another example SET NOCOUNT ON; SELECT CUSTOMERS.CUSTOMERID, CUSTOMERS.COMPANYNAME, ORDERS.ORDERID, ORDERS.ORDERDATE, [ORDER DETAILS].PRODUCTID, [ORDER DETAILS].QUANTITY FROM CUSTOMERS, ORDERS, [ORDER DETAILS] WHERE CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID AND ORDERS.ORDERID=[ORDER DETAILS].ORDERID AND ORDERS.ORDERID=10258 FOR XML AUTO, ELEMENTS

XML • So the output… • Gets represented as an XML document as: CUSTOMERID COMPANYNAME ORDERID ORDERDATE PRODUCTID QUANTITY ---------- ----------- -------- ---------- --------- -------- ERNSH Ernst Handel 10258 1996-07-17 2 50 ERNSH Ernst Handel 10258 1996-07-17 5 65 ERNSH Ernst Handel 10258 1996-07-17 32 6

XML

XML: Basic Syntax • How to specify relationships? • Without a DTD, we can use attributes to identify PKs and FKs. • ID and IDREF

<Northwind> <E> <EMPLOYEEID ID="1"> <LASTNAME>Davolio</LASTNAME> </EMPLOYEEID> <O> <ORDERID>10400</ORDERID> <CUSTOMERID>EASTC</CUSTOMERID> <SHIPCITY>London</SHIPCITY> <ORDERDATE>1997-01-01T00:00:00</ORDERDATE> <EMPLOYEEID IDREF="1"/> </O> <O> <ORDERID>10401</ORDERID> <CUSTOMERID>RATTC</CUSTOMERID> <SHIPCITY>Albuquerque</SHIPCITY> <ORDERDATE>1997-01-01T00:00:00</ORDERDATE> <EMPLOYEEID IDREF="1"/> </O> </E> <E> <EMPLOYEEID ID="8"> <LASTNAME>Callahan</LASTNAME> </EMPLOYEEID> <O> <ORDERID>10402</ORDERID> <CUSTOMERID>ERNSH</CUSTOMERID> <SHIPCITY>Graz</SHIPCITY> <ORDERDATE>1997-01-02T00:00:00</ORDERDATE> <EMPLOYEEID IDREF="8"/> </O> </E> </Northwind> Primary Key Foreign Key Empty Element

XML: Basic Syntax • ID and IDREF can be used to represent graphs • ERDs • Minimum and maximum cardinalities can be defined • <EMPLOYEEID IDREF="1“ maxOccurs = “unbounded”/>

XML • But again, is it sufficient for an XML document to well-formed? • No, we have to validate the data conforms to the structure. • In databases we have mechanisms for maintaining integrity and consistency. • In XML, the mechanisms are • Document Type Definition (DTD) • XML Schema

DTD • The DTD serves as a grammar for the underlying XML document • identifies the root document tag • declares what tags are permitted • the structure of the tags • the relations among tags

<!DOCTYPE Northwind [ <!ELEMENT Northwind (EMPLOLYEES*)> <!ELEMENT EMPLOYEES (EMPLOYEEID, LASTNAME, FIRSTNAME)> <!ELEMENT EMPLOYEEID (#PCDATA)> <!ELEMENT LASTNAME (#PCDATA)> <!ELEMENT FIRSTNAME (#PCDATA)> ]> <Northwind> <EMPLOYEES> <EMPLOYEEID>1</EMPLOYEEID> <LASTNAME>Davolio</LASTNAME> <FIRSTNAME>Nancy</FIRSTNAME> </EMPLOYEES> <EMPLOYEES> <EMPLOYEEID>2</EMPLOYEEID> <LASTNAME>Fuller</LASTNAME> <FIRSTNAME>Andrew</FIRSTNAME> </EMPLOYEES> <EMPLOYEES> <EMPLOYEEID>3</EMPLOYEEID> <LASTNAME>Leverling</LASTNAME> <FIRSTNAME>Janet</FIRSTNAME> </EMPLOYEES> </Northwind> The first line denotes the root node of the XML document. The second line specifies <Northwind> can have an arbitrary number of <EMPLOYEES>. The third line specifies the sub-elements of <EMPLOYEES> The remaining lines define each sub-element. #PCDATA stands for Parsed Character Data. All XML content is character string.

DTD • The order of elements in the <!Element > tag are meaningful. • To be validated, the tags in the XML document must conform to the order specified in the DTD.

DTD <!DOCTYPE Orderlist [ <!ELEMENT Orderlist (Customers)> <!ELEMENT Customers (CustomerID, CompanyName, Orders)> <!ELEMENT CustomerID (#PCDATA)> <!ELEMENT CompanyName (#PCDATA)> <!ELEMENT Orders (OrderID, OrderDate,ORDER_x0020_DETAILS)> <!ELEMENT OrderID (#PCDATA)> <!ELEMENT OrderDate (#PCDATA)> <!ELEMENT Orders_x0020_DETAILS (ProductID, Quantity)> <!ELEMENT ProductID (#PCDATA)> <!ELEMENT Quantity (#PCDATA)> ]> <ORDERLIST> <CUSTOMERS>stuff…</CUSTOMERS> </ORDERLIST>

DTD • We specify relationships with ID and IDREF but in the <!ATTLIST > tag. • The <!ATTLIST > tag allows us to assert specifics about the type of the attribute.

<!DOCTYPE Northwind [ <!ELEMENT Northwind (E*)> <!ELEMENT EMPLOYEES (EMPLOYEEID, LASTNAME, O)> <!ATTLIST E EMPLOYEEID ID #REQUIRED> <!ELEMENT EMPLOYEEID (#PCDATA)> <!ELEMENT LASTNAME (#PCDATA)> <!ELEMENT O (ORDERID, CUSTOMERID, SHIPCITY, ORDERDATE, EMPLOYEEID)> <!ELEMENT ORDERID (#PCDATA)> <!ELEMENT SHIPCITY (#PCDATA)> <!ELEMENT ORDERDATE (#PCDATA)> <!ELEMENT EMPLOYEEID (#PCDATA)> <!ATTLIST E EMPLOYEEID IDREF #REQUIRED> ]> <Northwind> <E> <EMPLOYEEID>1</EMPLOYEEID> <LASTNAME>Davolio</LASTNAME> <O><ORDERID>10400</ORDERID> <CUSTOMERID>EASTC</CUSTOMERID> <SHIPCITY>London</SHIPCITY> <ORDERDATE>1997-01-01T00:00:00</ORDERDATE> <EMPLOYEEID>1</EMPLOYEEID></O> <O><ORDERID>10401</ORDERID> <CUSTOMERID>RATTC</CUSTOMERID> <SHIPCITY>Albuquerque</SHIPCITY> <ORDERDATE>1997-01-01T00:00:00</ORDERDATE> <EMPLOYEEID>1</EMPLOYEEID></O> </E> <E><EMPLOYEEID>8</EMPLOYEEID> <LASTNAME>Callahan</LASTNAME> <O><ORDERID>10402</ORDERID> <CUSTOMERID>ERNSH</CUSTOMERID> <SHIPCITY>Graz</SHIPCITY> <ORDERDATE>1997-01-02T00:00:00</ORDERDATE <EMPLOYEEID>8</EMPLOYEEID></O> </E> </Northwind>

XSL • XML separates • Document structure (DTD) • Content (Elements, Attributes) • Materialization (XSL) • XSL transforms XML into HTML • The data model for XSL is an ordered tree

XSL • XSL is defined as a set of template rules • Each rule consists of a pattern and a template • Transformation syntax permits conversion of labeled tree into HTML • For example

XML AJAX and ADO.Net