XML AJAX and ADO.Net

1
XML AJAX andADO.Net
2
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

3
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.

4
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

5
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
• Lets look at the XML portion first.

6
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

7
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

8
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.

9
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)

10
Two cultures collide

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

11
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

12
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.

13
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

14
Semi-structured Data
ltHTMLgt ltBODYgt ltTABLE BORDER1gt ltTRgtltTDgt1lt/TDgtltTDgtD
avoliolt/TDgtltTDgtNancylt/TDgtlt/TRgt ltTRgtltTDgt1lt/TDgtltTDgtF
ullerlt/TDgtltTDgtAndrewlt/TDgtlt/TRgt ltTRgtltTDgt1lt/TDgtltTDgtL
everlinglt/TDgtltTDgtJanetlt/TDgtlt/TRgt lt/TABLEgt lt/BODYgt
lt/HTMLgt
15
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.
ltHTMLgt ltBODYgt ltTABLE BORDER1gt ltTRgtltTDgt1lt/TDgtltTDgtD
avoliolt/TDgtltTDgtNancylt/TDgtlt/TRgt ltTRgtltTDgt1lt/TDgtltTDgtF
ullerlt/TDgtltTDgtAndrewlt/TDgtlt/TRgt ltTRgtltTDgt1lt/TDgtltTDgtL
everlinglt/TDgtltTDgtJanetlt/TDgtlt/TRgt lt/TABLEgt lt/BODYgt
lt/HTMLgt
16
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

17
Semi-structured Data

• This confounding doesnt 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

18
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 isnt enough
• Documents must also follow rules

19
XML

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

20
XML Basic Syntax

• Elements
• A piece of text bounded by matching tags
• ltemployeegtHaroldlt/employeegt
• ltelementgtcontentlt/elementgt
• Elements (tags) can be anything
• Elements can contain elements as well as content

21
XML Basic Syntax

• Sub-elements
• ltEMPLOYEESgt
• ltEMPLOYEEIDgt1lt/EMPLOYEEIDgt
• ltLASTNAMEgtDavoliolt/LASTNAMEgt
• ltFIRSTNAMEgtNancylt/FIRSTNAMEgt
• lt/EMPLOYEESgt
• EmployeeID, LastName, and FirstName are
  sub-elements of Employee

22
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

23
XML Basic Syntax

• Set nocount on
• SELECT EMPLOYEEID, LASTNAME, FIRSTNAME
• FROM EMPLOYEES
• FOR XML AUTO, ELEMENTS
• Ill discuss this syntax later.

24
XML Basic Syntax
25
ltHTMLgt ltBODYgt ltTABLE BORDER1gt ltTRgtltTDgt1lt/TDgtltTDgtD
avoliolt/TDgtltTDgtNancylt/TDgtlt/TRgt ltTRgtltTDgt1lt/TDgtltTDgtF
ullerlt/TDgtltTDgtAndrewlt/TDgtlt/TRgt ltTRgtltTDgt1lt/TDgtltTDgtL
everlinglt/TDgtltTDgtJanetlt/TDgtlt/TRgt lt/TABLEgt lt/BODYgt
lt/HTMLgt
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.
26
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

27
SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM
EMPLOYEES FOR XML RAW
Using the RAW option, the XML content can be
represented as attributes.
28
In this case, Ive include the data type of the
element. Go to http//www.w3.org/2001/XMLSchema.xs
d for the current standards.
29
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, lets look at some more basic
  structure.

30
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

31
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
32
SELECT E.EMPLOYEEID, LASTNAME, ORDERID,
C.CUSTOMERID, SHIPCITY, COMPANYNAME FROM
EMPLOYEES E, ORDERS O, CUSTOMERS C WHERE
E.EMPLOYEEID O.EMPLOYEEID AND
C.CUSTOMERIDO.CUSTOMERID AND ORDERDATE
BETWEEN '1997-01-01' AND '1997-01-02' FOR XML
AUTO, ELEMENTS
33
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.CUSTOMERIDORDERS.
CUSTOMERID AND ORDERS.ORDERIDORDER
DETAILS.ORDERID AND ORDERS.ORDERID10258 F
OR XML AUTO, ELEMENTS
34
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
35
XML
36
XML
37
XML Basic Syntax

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

38
ltNorthwindgt ltEgt ltEMPLOYEEID ID"1"gt
ltLASTNAMEgtDavoliolt/LASTNAMEgt
lt/EMPLOYEEIDgt ltOgt ltORDERIDgt10400lt/ORDERIDgt ltCUS
TOMERIDgtEASTClt/CUSTOMERIDgt ltSHIPCITYgtLondonlt/SHIP
CITYgt ltORDERDATEgt1997-01-01T000000lt/ORDERDATEgt
ltEMPLOYEEID IDREF"1"/gt lt/Ogt ltOgt ltORDERIDgt1040
1lt/ORDERIDgt ltCUSTOMERIDgtRATTClt/CUSTOMERIDgt ltSHIP
CITYgtAlbuquerquelt/SHIPCITYgt ltORDERDATEgt1997-01-01
T000000lt/ORDERDATEgt ltEMPLOYEEID
IDREF"1"/gt lt/Ogt lt/Egt ltEgt ltEMPLOYEEID ID"8"gt
ltLASTNAMEgtCallahanlt/LASTNAMEgt
lt/EMPLOYEEIDgt ltOgt ltORDERIDgt10402lt/ORDERIDgt ltCUS
TOMERIDgtERNSHlt/CUSTOMERIDgt ltSHIPCITYgtGrazlt/SHIPCI
TYgt ltORDERDATEgt1997-01-02T000000lt/ORDERDATEgt lt
EMPLOYEEID IDREF"8"/gt lt/Ogt lt/Egt lt/Northwindgt
Primary Key
Foreign Key
Empty Element
39
(No Transcript)
40
XML Basic Syntax

• ID and IDREF can be used to represent graphs
• ERDs
• Minimum and maximum cardinalities can be defined
• ltEMPLOYEEID IDREF"1 maxOccurs unbounded/gt

41
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

42
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

43
lt!DOCTYPE Northwind lt!ELEMENT Northwind
(EMPLOLYEES)gt lt!ELEMENT EMPLOYEES (EMPLOYEEID,
LASTNAME, FIRSTNAME)gt lt!ELEMENT EMPLOYEEID
(PCDATA)gt lt!ELEMENT LASTNAME (PCDATA)gt lt!ELEME
NT FIRSTNAME (PCDATA)gt gt ltNorthwindgt ltEMPLOYEES
gt ltEMPLOYEEIDgt1lt/EMPLOYEEIDgt ltLASTNAMEgtDavolio
lt/LASTNAMEgt ltFIRSTNAMEgtNancylt/FIRSTNAMEgt lt/EMPL
OYEESgt ltEMPLOYEESgt ltEMPLOYEEIDgt2lt/EMPLOYEEIDgt
ltLASTNAMEgtFullerlt/LASTNAMEgt ltFIRSTNAMEgtAndrewlt/
FIRSTNAMEgt lt/EMPLOYEESgt ltEMPLOYEESgt ltEMPLOYEEI
Dgt3lt/EMPLOYEEIDgt ltLASTNAMEgtLeverlinglt/LASTNAMEgt
ltFIRSTNAMEgtJanetlt/FIRSTNAMEgt lt/EMPLOYEESgt lt/Nor
thwindgt
The first line denotes the root node of the XML
document. The second line specifies ltNorthwindgt
can have an arbitrary number of ltEMPLOYEESgt. The
third line specifies the sub-elements of
ltEMPLOYEESgt The remaining lines define each
sub-element.
PCDATA stands for Parsed Character Data. All XML
content is character string.
44
DTD

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

45
DTD
lt!DOCTYPE Orderlist lt!ELEMENT Orderlist
(Customers)gt lt!ELEMENT Customers (CustomerID,
CompanyName, Orders)gt lt!ELEMENT CustomerID
(PCDATA)gt lt!ELEMENT CompanyName
(PCDATA)gt lt!ELEMENT Orders (OrderID,
OrderDate,ORDER_x0020_DETAILS)gt lt!ELEMENT
OrderID (PCDATA)gt lt!ELEMENT OrderDate
(PCDATA)gt lt!ELEMENT Orders_x0020_DETAILS
(ProductID, Quantity)gt lt!ELEMENT ProductID
(PCDATA)gt lt!ELEMENT Quantity
(PCDATA)gt gt ltORDERLISTgt ltCUSTOMERSgtstufflt/CUSTO
MERSgt lt/ORDERLISTgt
46
DTD

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

47
lt!DOCTYPE Northwind lt!ELEMENT Northwind
(E)gt lt!ELEMENT EMPLOYEES (EMPLOYEEID, LASTNAME,
O)gt lt!ATTLIST E EMPLOYEEID ID
REQUIREDgt lt!ELEMENT EMPLOYEEID
(PCDATA)gt lt!ELEMENT LASTNAME (PCDATA)gt lt!ELEME
NT O (ORDERID, CUSTOMERID, SHIPCITY, ORDERDATE,
EMPLOYEEID)gt lt!ELEMENT ORDERID
(PCDATA)gt lt!ELEMENT SHIPCITY (PCDATA)gt lt!ELEME
NT ORDERDATE (PCDATA)gt lt!ELEMENT EMPLOYEEID
(PCDATA)gt lt!ATTLIST E EMPLOYEEID IDREF
REQUIREDgt gt ltNorthwindgt ltEgt ltEMPLOYEEIDgt1lt/EMPLO
YEEIDgt ltLASTNAMEgtDavoliolt/LASTNAMEgt ltOgtltORDERIDgt1
0400lt/ORDERIDgt ltCUSTOMERIDgtEASTClt/CUSTOMERIDgt ltS
HIPCITYgtLondonlt/SHIPCITYgt ltORDERDATEgt1997-01-01T0
00000lt/ORDERDATEgt ltEMPLOYEEIDgt1lt/EMPLOYEEIDgtlt/O
gt ltOgtltORDERIDgt10401lt/ORDERIDgt ltCUSTOMERIDgtRATTClt
/CUSTOMERIDgt ltSHIPCITYgtAlbuquerquelt/SHIPCITYgt ltO
RDERDATEgt1997-01-01T000000lt/ORDERDATEgt ltEMPLOYE
EIDgt1lt/EMPLOYEEIDgtlt/Ogt lt/Egt ltEgtltEMPLOYEEIDgt8lt/EMPL
OYEEIDgt ltLASTNAMEgtCallahanlt/LASTNAMEgt ltOgtltORDERID
gt10402lt/ORDERIDgt ltCUSTOMERIDgtERNSHlt/CUSTOMERIDgt
ltSHIPCITYgtGrazlt/SHIPCITYgt ltORDERDATEgt1997-01-02T0
00000lt/ORDERDATE ltEMPLOYEEIDgt8lt/EMPLOYEEIDgtlt/Ogt
lt/Egt lt/Northwindgt
48
(No Transcript)
49
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

50
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

51
Content can easily be generated from Northwind
database dynamically. But again, the problem is
how to get this back into the database or a
different database.
ltHTMLgt ltBODYgt ltH2gtEmployee List using
HTMLlt/H2gt ltTABLE BORDER"1"gt ltTRgtltTDgtEMPLOYEEIDlt/T
DgtltTDgtLASTNAMElt/TDgtltTDgtFIRSTNAMElt/TDgtlt/TRgt ltTRgtltTD
gt1lt/TDgtltTDgtDavoliolt/TDgtltTDgtNancylt/TDgtlt/TRgt ltTRgtltTD
gt2lt/TDgtltTDgtFullerlt/TDgtltTDgtAndrewlt/TDgtlt/TRgt ltTRgtltTD
gt3lt/TDgtltTDgtLeverlinglt/TDgtltTDgtJanetlt/TDgtlt/TRgt lt/TAB
LEgt lt/BODYgt lt/HTMLgt
52
lt?xml version"1.0" encoding"ISO-8859-1"?gt lt?xml-
stylesheet type"text/xsl" href"employeesDTD.xsl"
?gt lt!DOCTYPE Northwind lt!ELEMENT Northwind
(EMPLOLYEES)gt lt!ELEMENT EMPLOYEES (EMPLOYEEID,
LASTNAME, FIRSTNAME)gt lt!ELEMENT EMPLOYEEID
(PCDATA)gt lt!ELEMENT LASTNAME (PCDATA)gt lt!ELEME
NT FIRSTNAME (PCDATA)gt gt ltNorthwindgt ltEMPLOYEES
gt ltEMPLOYEEIDgt1lt/EMPLOYEEIDgt ltLASTNAMEgtDavolio
lt/LASTNAMEgt ltFIRSTNAMEgtNancylt/FIRSTNAMEgt lt/EMPL
OYEESgt ltEMPLOYEESgt ltEMPLOYEEIDgt2lt/EMPLOYEEIDgt
ltLASTNAMEgtFullerlt/LASTNAMEgt ltFIRSTNAMEgtAndrewlt/
FIRSTNAMEgt lt/EMPLOYEESgt ltEMPLOYEESgt ltEMPLOYEEI
Dgt3lt/EMPLOYEEIDgt ltLASTNAMEgtLeverlinglt/LASTNAMEgt
ltFIRSTNAMEgtJanetlt/FIRSTNAMEgt lt/EMPLOYEESgt lt/Nor
thwindgt
Ive included a reference to an XSL stylesheet.
53
The stylesheet transforms the XML tree into a
table.
54
lt?xml version"1.0" encoding"ISO-8859-1"?gt ltxsls
tylesheet version"1.0" xmlnsxsl"http//www.w3.
org/1999/XSL/Transform"gt ltxsltemplate
match"/"gt ltHTMLgt ltHEADgtltTITLEgtEmployee List
using XSL Transformationlt/TITLEgtlt/HEADgt ltBODYgt
ltH2gtEmployee List using XSL Transformationlt/H2gt
ltTABLE BORDER"1"gt ltTRgtltTDgtEMPLOYEEIDlt
/TDgtltTDgtLASTNAMElt/TDgtltTDgtFIRSTNAMElt/TDgtlt/TRgt
ltxslfor-each select"Northwind/EMPLOYEES"gt
ltTRgt ltTDgtltxslvalue-of
select"EMPLOYEEID"/gtlt/TDgt
ltTDgtltxslvalue-of select"LASTNAME"/gtlt/TDgt
ltTDgtltxslvalue-of select"FIRSTNAME"/gtlt/TDgt
lt/TRgt lt/xslfor-eachgt
lt/TABLEgt lt/BODYgt lt/HTMLgt lt/xsltemplategt lt/xsl
stylesheetgt
55
lt?xml version"1.0" encoding"ISO-8859-1"?gt ltxsls
tylesheet version"1.0" xmlnsxsl"http//www.w3.
org/1999/XSL/Transform"gt ltxsltemplate
match"/"gt ltHTMLgt ltHEADgtltTITLEgtEmployee List
using XSL Transformationlt/TITLEgtlt/HEADgt ltBODYgt
ltH2gtEmployee List using XSL Transformationlt/H2gt
ltTABLE BORDER"1"gt ltTRgtltTDgtEMPLOYEEIDlt
/TDgtltTDgtLASTNAMElt/TDgtltTDgtFIRSTNAMElt/TDgtlt/TRgt
ltxslfor-each select"Northwind/EMPLOYEES"gt
ltTRgt ltTDgtltxslvalue-of
select"EMPLOYEEID"/gtlt/TDgt
ltTDgtltxslvalue-of select"LASTNAME"/gtlt/TDgt
ltTDgtltxslvalue-of select"FIRSTNAME"/gtlt/TDgt
lt/TRgt lt/xslfor-eachgt
lt/TABLEgt lt/BODYgt lt/HTMLgt lt/xsltemplategt lt/xsl
stylesheetgt
Define the type of document with an xsl begin
and end tag with a reference to the w3 reference
schema.
56
lt?xml version"1.0" encoding"ISO-8859-1"?gt ltxsls
tylesheet version"1.0" xmlnsxsl"http//www.w3.
org/1999/XSL/Transform"gt ltxsltemplate
match"/"gt ltHTMLgt ltHEADgtltTITLEgtEmployee List
using XSL Transformationlt/TITLEgtlt/HEADgt ltBODYgt
ltH2gtEmployee List using XSL Transformationlt/H2gt
ltTABLE BORDER"1"gt ltTRgtltTDgtEMPLOYEEIDlt
/TDgtltTDgtLASTNAMElt/TDgtltTDgtFIRSTNAMElt/TDgtlt/TRgt
ltxslfor-each select"Northwind/EMPLOYEES"gt
ltTRgt ltTDgtltxslvalue-of
select"EMPLOYEEID"/gtlt/TDgt
ltTDgtltxslvalue-of select"LASTNAME"/gtlt/TDgt
ltTDgtltxslvalue-of select"FIRSTNAME"/gtlt/TDgt
lt/TRgt lt/xslfor-eachgt
lt/TABLEgt lt/BODYgt lt/HTMLgt lt/xsltemplategt lt/xsl
stylesheetgt
Apply template to entire document starting with
the root node.
57
lt?xml version"1.0" encoding"ISO-8859-1"?gt ltxsls
tylesheet version"1.0" xmlnsxsl"http//www.w3.
org/1999/XSL/Transform"gt ltxsltemplate
match"/"gt ltHTMLgt ltHEADgtltTITLEgtEmployee List
using XSL Transformationlt/TITLEgtlt/HEADgt ltBODYgt
ltH2gtEmployee List using XSL Transformationlt/H2gt
ltTABLE BORDER"1"gt ltTRgtltTDgtEMPLOYEEIDlt
/TDgtltTDgtLASTNAMElt/TDgtltTDgtFIRSTNAMElt/TDgtlt/TRgt
ltxslfor-each select"Northwind/EMPLOYEES"gt
ltTRgt ltTDgtltxslvalue-of
select"EMPLOYEEID"/gtlt/TDgt
ltTDgtltxslvalue-of select"LASTNAME"/gtlt/TDgt
ltTDgtltxslvalue-of select"FIRSTNAME"/gtlt/TDgt
lt/TRgt lt/xslfor-eachgt
lt/TABLEgt lt/BODYgt lt/HTMLgt lt/xsltemplategt lt/xsl
stylesheetgt
Hard code the basic structure of the table. This
could be done in steps. The heading values could
be retrieved using the xslELEMENT.
58
lt?xml version"1.0" encoding"ISO-8859-1"?gt ltxsls
tylesheet version"1.0" xmlnsxsl"http//www.w3.
org/1999/XSL/Transform"gt ltxsltemplate
match"/"gt ltHTMLgt ltHEADgtltTITLEgtEmployee List
using XSL Transformationlt/TITLEgtlt/HEADgt ltBODYgt
ltH2gtEmployee List using XSL Transformationlt/H2gt
ltTABLE BORDER"1"gt ltTRgtltTDgtEMPLOYEEIDlt
/TDgtltTDgtLASTNAMElt/TDgtltTDgtFIRSTNAMElt/TDgtlt/TRgt
ltxslfor-each select"Northwind/EMPLOYEES"gt
ltTRgt ltTDgtltxslvalue-of
select"EMPLOYEEID"/gtlt/TDgt
ltTDgtltxslvalue-of select"LASTNAME"/gtlt/TDgt
ltTDgtltxslvalue-of select"FIRSTNAME"/gtlt/TDgt
lt/TRgt lt/xslfor-eachgt
lt/TABLEgt lt/BODYgt lt/HTMLgt lt/xsltemplategt lt/xsl
stylesheetgt
Scroll through each element in the tree starting
with the Northwind/EMPLOYEES sub-elements. For
each sub-element, create a row and column in the
table.
59
XSL

• I embedded the DTD in the XML document, but if I
  had stored it as a separate file, the Employee
  List materialization would involve three distinct
  files
• Employees.xml (content)
• Employees.dtd (structure)
• Employees.xsl (presentation)

60
XML data exchange

• We can create an XML document for the Product
  table for all products in category 4 that can be
  imported into the database.

SET NOCOUNT ON SELECT PRODUCTID, PRODUCTNAME,
UNITSINSTOCK FROM PRODUCTS WHERE
CATEGORYID4 FOR XML AUTO, ELEMENTS
61
XML

• We can create an XML document for the Product
  table for all products in category 4.

PRODUCTID PRODUCTNAME
UNITSINSTOCK ----------- --------------------
-------------------- ------------ 11
Queso Cabrales 22 12
Queso Manchego La Pastora
86 31 Gorgonzola Telino
0 32 Mascarpone Fabioli
9 33 Geitost
112 59 Raclette
Courdavault 79 60
Camembert Pierrot 19 69
Gudbrandsdalsost
26 71 Flotemysost
26 72 Mozzarella di Giovanni
14
62
XML
63
XML

• This XML can then be parsed and used by the DBMS.
  In this case, to insert the records into the
  local database.

SET NOCOUNT ON DECLARE _at_hdoc int DECLARE _at_doc
varchar(4000) SET _at_doc 'ltPgtltPRODUCTSgtltPRODUCTIDgt1
1lt/PRODUCTIDgtstufflt/ROOTgt' EXEC
sp_xml_preparedocument _at_hdoc OUTPUT, _at_doc SELECT
INTO ProductListCategory4 FROM OPENXML (_at_hdoc,
'/P/PRODUCTS',2) WITH (PRODUCTID
int, PRODUCTNAME varchar(25),
UNITSINSTOCK int) EXEC
sp_xml_removedocument _at_hdoc
64
XML

• Sp_xml_preparedocument is a system stored
  procedure that creates an internal tree
  representation of the XML document that can then
  be represented as a cursor or table using
  OPENXML.
• OPENXML creates a cursor or rowset representation
  that can then me used in a SQL expression.
• Sp_xml_removedocument clears the rows et from
  memory.

65
XML

• Returning to the original claim that XML will
  change everything
• How is XML an improvement over say SQL Servers
  DTS?
• Saving as comma delimited flat file?

66
XML

• XML is a language for manipulating the what
  rather the how of data.
• Standard means for representing domains
• Standard means of expressing views
• Clean separation of structure, content, and
  formatting
• Facility for document validity checking

67
Extensible Style Language Transformation XMLT

• XMLT is used to transform one document into
  another document
• Mapping company As document (say an order) into
  company Bs DTD or schema

68
XML Schema

• XML Schema is the preferred method for defining
  document structure
• The schema itself is an XML document
• A document that conforms to an XML Schema is
  termed schema-valid.
• XML Schema documents are validated against the
  root schema at www.w3.org

69
Does XML provide a reasonable solution?

• 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

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