1 / 26

Using SQL Queries to Generate XML-Formatted Data

Using SQL Queries to Generate XML-Formatted Data . Joline Morrison Mike Morrison Department of Computer Science University of Wisconsin-Eau Claire. Outline. Study motivations Overview of XML XML SQL query syntax Oracle SQL Server Conclusions Platform strengths/weaknesses

shana
Download Presentation

Using SQL Queries to Generate XML-Formatted Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using SQL Queries to Generate XML-Formatted Data Joline Morrison Mike Morrison Department of Computer Science University of Wisconsin-Eau Claire

  2. Outline • Study motivations • Overview of XML • XML SQL query syntax • Oracle • SQL Server • Conclusions • Platform strengths/weaknesses • Suggestions for student activities

  3. Study Motivations • XML has become the de facto standard for sharing data across diverse applications and hardware/software platforms • SQL-2003 ISO specifies standards for forming queries that retrieve XML-formatted data • But most organizational data is stored in relational databases...

  4. XML Overview • Defines structured data using markup language notation • Structured data: Data defined in a specific and unambiguous format • Markup language: • Uses tags or other symbols to specify document formatting • Tags are defined in a document type definition (DTD)

  5. XML Features • XML DTD allows developers to define custom tags to define the syntax, semantics, and structure of data • XML documents are text files • Can be shared across different applications and hardware/software platforms

  6. Example XML Document <?xml version="1.0" encoding="UTF-8" ?> <books> <book isbn="99999-99999"> <title>CS 365: A Visual History</title> <authors> <author id="100"> <firstname>Tom</firstname> <lastname>Moore</lastname> </author> <author id="101"> <firstname>Leonard</firstname> <lastname>Larsen</lastname> </author> </authors> <publisher>UWEC-CS Press</publisher> <publishyear>2000</publishyear> <price type="USD">10.00</price> </book> Prolog Element value Aggregated data element Attribute value

  7. Representing Data Relationships in XML Documents <book isbn="99999-99999"> <title>CS 365: A Visual History</title> <authors> <author id="100"> <firstname>Tom</firstname> <lastname>Moore</lastname> </author> <author id="101"> <firstname>Leonard</firstname> <lastname>Larsen</lastname> </author> </authors> • Relationships limited to 1:M • Relationships must be hierarchical

  8. Outline • Research motivations • Overview of XML • XML SQL query syntax • Oracle • SQL Server • Conclusions • Platform strengths/weaknesses • Suggestions for student activities

  9. XML SQL Queries • Approach: create SQL queries that retrieve relational database data and "wrap" it in predefined XML tags • ISO-2003 SQL standards specify required functionality but don't prescribe syntax • Different vendors implement the same functionality quite differently!

  10. Example XML SQL Queries • Operations: • Format values as elements & attributes • Create aggregate elements to represent relationships • Platforms: Oracle & SQL Server

  11. Oracle:Formatting Data as Elements • XMLElement function creates a new XML element • Parameters specify element names and associated data values SELECT XMLElement("department", department_name) FROMuniversity_department ORDER BY department_name; <department>Accounting</department> <department>Chemistry</department> <department>Computer Science</department> …

  12. Oracle:Formatting Data as Attributes • You first use XMLElementto create the element • XMLAttributesfunction retrieves and formats one or more data values as attributes SELECT XMLElement("department", XMLAttributes(department_id AS "id", department_name AS "name")) FROM university_department ORDER BY department_name; <department id="2" name="Accounting"></department> <department id="5" name="Chemistry"></department> <department id="4" name="Computer Science"></department>

  13. Oracle:Creating Aggregate Data • Create the parent element using XMLElement • Retrieve the child values as elements using the XMLAggfunction SELECT XMLElement("department", XMLAgg(XMLElement("course", course_name))) FROM university_department a INNER JOIN university_course b ON a.department_id = b.department_id GROUP BY department_name; <department> <course>ACCT 201</course> <course>ACCT 312</course> </department> <department><course>CHEM 205</course></department> <department><course>CS 245</course></department>

  14. Oracle:Nesting XML Functions • Oracle allows you to nest XML functions to retrieve data in a variety of formats... SELECT XMLElement("department", XMLAttributes(department_name AS "name"), XMLElement("courses", (XMLAgg(XMLElement("course", course_name))))) FROM university_department a INNER JOIN university_course b ON a.department_id = b.department_id WHERE a.department_id = 1 GROUP BY department_name; <department name="Management Information Systems"> <courses> <course>MIS 240</course> <course>MIS 310</course> <course>MIS 344</course> </courses> </department>

  15. SQL Server:General Approach SELECT ... FOR XML Mode[, ELEMENTS] • Mode values: • RAW: returns each record as an XML element enclosed in a <row> element • AUTO: returns each record as a named XML element and hierarchically nests child nodes from JOIN queries • EXPLICIT: provides precise control on how data values are formatted • ELEMENTS option: formats each field as a separate element

  16. SQL Server:Formatting Data as Elements • RAWmode: SELECT DepartmentName, CourseName FROM UniversityDepartment a INNER JOIN UniversityCourse b ON a.DepartmentID = b.DepartmentID ORDER BY DepartmentName, CourseName FOR XML RAW <row DepartmentName="Accounting" CourseName="ACCT 201" /> <row DepartmentName="Accounting" CourseName="ACCT 312" /> <row DepartmentName="Chemistry" CourseName="CHEM 205" />

  17. SQL Server:Formatting Data as Elements • RAW, ELEMENTS option: SELECT DepartmentName, CourseName FROM UniversityDepartment a INNER JOIN UniversityCourse b ON a.DepartmentID = b.DepartmentID ORDER BY DepartmentName, CourseName FOR XML RAW, ELEMENTS <row> <DepartmentName>Accounting</DepartmentName> <CourseName>ACCT 201</CourseName> </row> <row> <DepartmentName>Accounting</DepartmentName> <CourseName>ACCT 312</CourseName> </row>

  18. SQL Server:Formatting Data as Elements • AUTO, ELEMENTS option: SELECT DepartmentName, CourseName FROM UniversityDepartment dept INNER JOIN UniversityCourse course ON dept.DepartmentID = course.DepartmentID ORDER BY DepartmentName, CourseName FOR XML AUTO, ELEMENTS <dept> <DepartmentName>Accounting</DepartmentName> <course> <CourseName>ACCT 201</CourseName> </course> <course> <CourseName>ACCT 312</CourseName> </course> </dept> <dept>

  19. SQL Server:Formatting Data as Attributes • AUTO mode (remove ELEMENTS option): SELECT DepartmentName, CourseName FROM UniversityDepartment dept INNER JOIN UniversityCourse course ON dept.DepartmentID = course.DepartmentID ORDER BY DepartmentName, CourseName FOR XML AUTO <dept DepartmentName="Accounting"> <course CourseName="ACCT 201" /> <course CourseName="ACCT 312" /> </dept> <dept DepartmentName="Chemistry"> <course CourseName="CHEM 205" /> </dept> <dept DepartmentName="Computer Science"> <course CourseName="CS 245" /> </dept>

  20. SQL Server:XML EXPLICIT mode • Allows you to specify parent and child elements precisely • Each level is defined within a separate query • Queries are joined using the UNION operator • Level 1 query: SELECT 1 as tag, NULL as parent, DepartmentName AS [dept!1!name!element] FROM UniversityDepartment ORDER BY DepartmentName FOR XML EXPLICIT <dept> <name>Accounting</name> </dept> <dept> <name>Chemistry</name> </dept>

  21. XML EXPLICIT query with 2 levels SELECT 1 As tag, NULL As parent, DepartmentName As [dept!1!dname], NULL As [course!2!cname!element], NULL As [course!2!title!element] FROM UniversityDepartment UNION SELECT 2 As tag, 1 As parent, DepartmentName,CourseName, CourseTitle FROM UniversityDepartment a INNER JOIN UniversityCourse b ON a.DepartmentID = b.DepartmentID ORDER BY [dept!1!dname], [course!2!cname!element] FOR XML EXPLICIT <dept dname="Accounting"> <course> <cname>ACCT 201</cname><title>Accounting I</title> </course> <course> <cname>ACCT 312</cname> <title>Managerial Accounting</title> </course> </dept>

  22. Outline • Study motivations • Overview of XML • XML SQL query syntax • Oracle • SQL Server • Conclusions • Platform strengths/weaknesses • Suggestions for student activities

  23. Conclusions • Platform strengths/weaknesses • Oracle syntax seems a little shorter and cleaner overall • SQL Server automatically creates aggregate data from JOIN queries • SQL Server EXPLICIT mode provides precise formatting • At a high cost!

  24. Conclusions • Platform strengths/weaknesses (continued) • Oracle allows you to create aggregated data in a way that SQL Server does not: <department name="Management Information Systems"> <courses> <course>MIS 240</course> <course>MIS 310</course> <course>MIS 344</course> </courses> </department>

  25. Conclusions • Suggestions for student activities • Manually create XML-formatted data for a series of related database tables • Create queries in both Oracle and SQL Server to retrieve XML-formatted data and analyze syntax differences • Generate XML-formatted data and display it in a browser • Generate XML-formatted data and transform it into and HTML document using XSLTs

  26. Additional Resources • Scripts to create databases in Oracle & SQL Server • Electronic copy of the paper • Electronic copy of the slideshow http://www.cs.uwec.edu/~morrisjp/Public/Conferences/MICS

More Related