21-08-2014, 03:53 PM
XML DATABASE
XML DATABASE.docx (Size: 163.5 KB / Downloads: 12)
1. Introduction
Although XML is certainly a great storage format for shuttling data around the Web, most web applications store their data in relational databases. Knowing this, it stands to reason that many web applications will end up accessing data from a database yet interacting with it and sharing it with other applications as XML data. This has a lot do with the fact that XML can be used not only as a document format, but also as a way to represent data in a highly structured manner. In this tutorial, you learn how relational databases work and how you can integrate your XML applications with relational databases. More specifically, you find out how to export database data as XML code, as well as how to make SQL queries on a database and format the results in XML
An XML database is a data persistence software system that allows data to be stored in XML format. These data can then be queried, exported and serialized into the desired format. XML databases are usually associated with document-oriented databases.
Two major classes of XML database exist
1. XML-enabled: these may either map XML to traditional database structures (such as a relational database), accepting XML as input and rendering XML as output, or more recently support native XML types within the traditional database. This term implies that the database processes the XML itself (as opposed to relying on middleware).
2. Native XML (NXD): the internal model of such databases depends on XML and uses XML documents as the fundamental unit of storage, which are, however, not necessarily stored in the form of text files.
2. A Quick Relational Database Primer
Before you can learn about relating XML to databases, you need to learn about databases themselves. When most people think of databases, they're thinking specifically about relational databases. All of the popular database products Microsoft SQL Server, Oracle, IBM DB2, MySQL use the relational model. In turn, most web and business applications use one relational database or another for data storage.
The relational database model is all about tables. All of the data is stored in a tabular format, and relationships between tables are expressed through data shared among those tables
3. Introduction to SQL
One term you can't go far into databases without encountering is SQL, which stands for Structured Query Language. SQL is the language used to retrieve, add, modify, and delete records in databases. Let's look at each of these features in turn.
3.1 Retrieving Records Using SELECT
Just about everything in SQL is carried out via a query, which is simply the act of communicating with the database according to an established set of SQL commands. The query used to retrieve data from a database is called the SELECT statement. It has several parts, not all of which are mandatory.
3.2 The WHERE Clause
Both of the previous queries simply return all of the records in the students table. Often, you'll want to constrain the result set so that it returns only those records you're actually interested in. The WHERE clause is used to specify which records in a table should be included in the results of a query. Here's an example:
SELECT student_name
FROM students
WHERE id_students = 1
3.3 Inserting Records
The INSERT statement is used to insert records into a table. The syntax is simple, especially if you plan on populating every column in a table. To insert a record into majors, use the following statement:
INSERT INTO majors
3.4 Updating Records
When you want to modify one or more records in a table, the UPDATE statement is used. Here's an example:
UPDATE students
SET classification = 'senior'
The previous SQL statement will work, but I bet you can figure out what's wrong with it. Nowhere is it specified which records to update. If you don't tell it which records to update, it just assumes that you want to update all of the records in the table, thus the previous query would turn all of the students into seniors. That's probably not what you have in mind. Fortunately, the UPDATE statement supports the WHERE clause, just like the SELECT statement.
UPDATE students
SET classification = 'senior'
WHERE id_students = 1
3.5 Deleting Records
The last SQL statement I'll discuss is the DELETE statement, which is similar to the UPDATE statement. It accepts a FROM clause, and optionally a WHERE clause. If you leave out the WHERE clause, it deletes all the records in the table. Here's an example:
DELETE FROM students
WHERE id_students = 1
4. Databases and XML
When you integrate XML with databases, the first question that you must look at is how you're using XML in your application. There are two broad categories of XML applications those that use XML for data storage, and those that use XML as a document format. The approach for database integration depends on which category your application falls into.
Although XML is commonly thought of as a document format, it's also very popular as a format for data storage. Many applications use XML files to store their configuration, as well as relying on remote procedure calling services like XML-RPC and SOAP to format the messages that they exchange using XML.
4.1 Resolving XML Data into Database Tables
The question you face when you integrate applications that use XML for data storage with relational databases is the degree to which you want to take advantage of the features of the relational database. If you simply insert entire XML documents into the database, you can't use advanced SQL features to retrieve specific bits of information from the XML documents.
Here's an XML document that is used to store information related to automobiles:
<dealership>
<automobile make="Buick" model="Century" color="blue">
<options>
<option>cruise control</option>
<option>CD player</option>
</options>
</automobile>
<automobile make="Ford" model="Thunderbird" color="red">
<options>
<option>convertible</option>
<option>leather interior</option>
<option>heated seats</option>
</options>
</automobile>
</dealership>
4.2 Storing XML Documents in a Database
If you're storing entire XML documents in a database, you don't need to worry about translating the XML document format into a tabular database structure. Instead, you just need to extract the information from the document that you need to use in the relational database world and create columns for that. As an example, if you store newspaper articles as XML documents, the section, headline, author, body, and perhaps more information will all be included in the XML document within their own tags. It is then possible to process the XML code to access each portion of the document.
If you store those documents in a database and plan on publishing them on the Web from that database, you may want to consider breaking up the XML data so that it can be retrieved more easily. For example, you might want separate columns for the section and writer so that you can write simple SQL statements that retrieve the documents based on those values. Either way, you would be retrieving XML code from the database, which is far different than the earlier automobile example where the database data has been translated from XML into pure database content.
5. Exporting an XML Document from a Database
If you need to pull data from a database for processing as XML on a one-time basis, or maybe periodically but not necessarily in real-time, you might consider just exporting the data manually. Most databases offer an "export as XML" option that converts a database table into a structured XML document with the database columns turned into XML tags. This is a very simple approach to quickly generating an XML document from a database that you might now otherwise be able to access without database tools.
I regularly use the MySQL database for online projects. MySQL is a very popular open source database that does a great job for small- to medium-scale applications. A nice front-end is available for MySQL called phpMyAdmin, which provides a web-based user interface for interacting with a MySQL database. phpMyAdmin provides a very easy-to-use export feature that will export any MySQL database as an XML document.
To get started exporting an XML document from a MySQL database, open the database in phpMyAdmin, and select the table you want to export. Then click the Export tab. Within the Export options, click XML to indicate that XML is the output data format. If you want to generate an XML file that is stored on the web server, click the Save As File option. Otherwise, just click the Go button to generate the XML code and view it directly in the browser. Figure 3 shows how the exported XML document is shown in a web browser.
6. Accessing Data from a Database as XML
Although manually exporting an XML document from a database can be useful, it isn't quite the same as drilling into a database via a SQL query and extracting exactly the data you need. A more realistic example would involve generating XML code on the fly based upon a SQL query. Fortunately, I have exactly such an example for you to check out.
The example you're about to see extracts data from a real database that I created to manage the statistics for my recreational hockey team, Music City Mafia. The database is a MySQL database that stores statistics for both games and players. In this example, you're only concerned with game data, which is stored in a database table called games. To access the data and initiate a SQL query, I'm using PHP, which is an open source scripting language used to create dynamic web pages. PHP has very good integration with MySQL, and is a great option for dynamic web page development that involves MySQL databases and XML.
7. Conclusion:
The purpose of this tutorial was to introduce the concepts behind relational databases and explain how to integrate them with your XML applications. First, I provided a brief introduction to the theory behind relational databases, which is important to understand when you're mixing them with a different sort of data structure, like the one provided by XML. I then provided a very brief overview of SQL, which is the language used to query all relational databases. I then described the issues that arise when you're integrating relational databases with XML applications, and explained how using XML for data storage and XML as a document format differ. Finally, I demonstrated how to export XML data from an existing database, as well as how to perform a SQL query on a database and format the results as an XML document.