Improvements Planned for XML Schemas November 2005 Hector Tello

1
Improvements Planned for XML SchemasNovember
2005Hector Tello
2

• 15 schemas were provided last year
• Several are no longer valid (Status Prior
  Year/Status Current Year)
• Directory and Metric schemas will remain largely
  unchanged - continued support for current XML
  users
• No plans to discontinue support of existing
  metric file schemas
• For 05-06, EDEN will only accept metric files
  that conform to the generic schemas

3

• Planned Activities
• Develop a complete set of validation schemas
  that are compliant with 05-06 XML specifications
• Leverage Wyomings work developing their own
  schemas
• In excess of 150 schemas will be supplied for
  05-06
• Provide XSLT Stylesheets for Metric files to
  translate to Release 1 format

4

• Planned Activities (contd)
• Incorporate ED standards
• Department of Education XML Steering Committee
  (DXSC)
• FUTURE Enhancing EDEN to accept metric files
  using validation schemas

5
Creating EDEN XML FilesNovember 2005Hector Tello
6

• Topics
• Extracting Data from a Relational Database
• Oracle
• SQL Server
• Using Other Tools

7

• Oracle
• Use PL/SQL (Old Habits Die Hard)
• Open cursor(s)
• Open an output file (UTL_FILE)
• Print data, line-by-line txt ltADDRESS
  ADDRESSTYPE address.type
  gtutl_file.put_line(outfile, txt)
• Disadvantage
• Tedious programming

8

• Oracle XML Output
• Release 8i Has limited XML support
• Release 9i - XML functions
• XMLElement a function to transform a relational
  value into an XML elementltCITYgtSpringfieldlt/CITYgt
  
• XMLAttributes relational value to attrib.
• XMLForest handles NULLs
• XMLAgg aggregation of results

9

• Oracle XML Output (contd)
• Simple Example

10

• Oracle XML Output (contd)
• School Directory (School_directory.sql)

11

• Oracle XML Output (contd)
• ltAGENCY FIPSSTATECODE"99" STATEAGENCYIDNUMBER"01
  " STATELEAIDNUMBER"1" NCESLEAIDNUMBER"999999"
  STATESCHOOLIDNUMBER"1000" NAME"Test School 1
  TYPE"1" PHONENUMBER"2105551212"
  WEBSITEADDRESS"www.eu.us/TestSchoo1"gtltADDRESS
  ADDRESSTYPE"LOC"gt ltLINE1gt100 Mainlt/LINE1gt
  ltCITYgtSpringfieldlt/CITYgt ltSTATEgtEUlt/STATEgt
  ltZIPCODEgt99999lt/ZIPCODEgtlt/ADDRESSgt

12

• Oracle XML Views
• Release 9.2.0.2
• Create XML views that can use relational tables
  or object-relational data
• Allows XML schema to be associatedto view
• CREATE VIEW school_directory of XMLTYPE XMLSCHEMA
  schdir.xsd ELEMENT FILETRANSMISSION AS
• SELECT

13

• Oracle XML Tables
• Create an XML table that matches the structure
  and rules of an XML schema
• CREATE TABLE school_directory of XMLTYPE
  XMLSCHEMA schdir.xsd ELEMENT FILETRANSMISSION
  
• Typically used when XML documents (instance) are
  being imported into the DB

14

• Microsoft SQL Server
• Supported by SQL Server 2000 with SQLXML (Release
  3.0, SP 3)
• Options
• Select FOR XML
• XSD Mapping

15

• SELECT FOR XML
• SELECT School.SchoolID, School.Name,
  School.Type, Address.Type, Address.Street1FRO
  M School JOIN SchoolAddress as Address ON
  School.SchoolID Address.SchoolIDFOR XML

16

• Supported options
• RAW
• AUTO
• EXPLICIT
• XMLDATA
• ELEMENTS

17

• Output using AUTO
• ltSchool SchoolID1 NameAdams HS Type1
  ltAddress TypeMAIL Street1100
  Main St. lt/Addressgt ltAddress TypeLOC
  Street1PO Box 10
  lt/Addressgtlt/Schoolgt

18

• SELECT . FOR XML EXPLICIT
• Provides the level of detail required for EDEN
  XML files
• User can specify Tag, Element or Attribute,
  Parent (position in hierarchy)
• Use UNION to add second or third level detail
  records

19

• EXPLICIT example
• SELECT 1 as Tag, NULL as Parent, SchoolID as
  AGENCY!1!STATESCHOOLIDNUMBER, NULL AS
  ADDRESS!2!City!element FROM SchoolUNION
  SELECT 2 as Tag, 1 as Parent, CityFROM
  SchoolAddressFOR XML EXPLICIT

20

• MSSQL XSD Mapping
• Uses a mapping schema to map data between
  relational tables and an XML document
• Used to create an XML view that returns an XML
  document representing the underlying table data
• Maps columns and tables to attributes and
  elements
• Maps relationships between tables

21

• Simple example elements and attributes
• ltxsd.element nameADDRESS sql.relationSchoo
  lAddress ltxsd.complexTypegt
  ltxsd.attribute nameADDRESSTYPE
  sql.fieldType typexsd.string /gt
  ltxsd.element nameLINE1
  sqlfieldStreet1 typexsd.string
  /gt lt/xsd.complexTypegtlt/xsd.elementgt

22

• Simple example defining relationships
• ltxsd.annotationgt ltxsd.appinfogt
  ltsqlrelationship parentSchool
  parent-keySchoolID
  childSchoolAddress
  child-keySchoolID /gt
• lt/xsd.appinfogtlt/xsd.annotationgt

23

• Using Other Tools
• Tools that map from databases to XML
• Tools that map from csv, tab delimited, and fixed
  width format files to XML
• Microsoft Excel
• XML databases

24

• Recall our relational database example

25

• You will need a XML schema fileSchool Directory
  SchoolDirectory.xsd

26

• Mapping from Relational DB to XML

27

• Mapping from Relational DB to XML Results

28

• Oops Had too many Gradelevels before

29

• Fixed

30

• Mapping Text Files (csv,tab,fixed) to XML

31

• Mapping Text Files (csv,tab,fixed) to XML

32

• Mapping Text Files (csv,tab,fixed) to XML

33
Recap Learning Objectives

• Provide trainees an introduction to XML and
  related technologies
• Discuss areas where EDENs use of XML can be
  enhanced
• Present technologies and tools that can be used
  to get started with the migration to XML
• Provide trainees time to ask questions, share
  successes and discuss challenges.