Welcome!

Industrial IoT Authors: Craig Lowell, Carmen Gonzalez, Elizabeth White, Pat Romanski, William Schmarzo

Related Topics: Industrial IoT

Industrial IoT: Article

Generating XML from Relational Database Tables

This Emerging Standard Enables to Generate XML Fragments from Relational Data

This article looks in detail at how to generate XML data from your relational database. Although the examples were run on Oracle, very little of the code is Oracle specific. You can easily use all the ideas and examples presented here in other relational databases. We did this project at University of Massachusetts Boston as part of the Electronic Field Guide (EFG) project.

XML is the de facto standard for data exchange. It's simple, Unicode based, and platform independent. XML is a metadata language; it contains information about the data. All these features make it an attractive standard for exchanging data.

Why Generate XML from Relational Data?
Today most data (80% or more), is stored in relational databases such as Oracle, DB2, SQL Server 2000, and others. The Internet and Web services are present in our daily lives. A tremendous amount of data is transferred over the Internet between businesses. Data transfers may happen within a company, between different branches, or between different companies and individuals. No matter which of these situations applies to you, it's very likely that you will be asked to ship your data in XML, or that you are already doing it. Relational Database Management Systems (RDBMS) are still the best way to hold data in bulk. In the following paragraph we will give some information about our project and related resources that we used to provide relational data in XML.

Our Project
Our XML project is part of the Electronic Field Guild (EFG). The EFG project is an object-oriented, Web-based database for the identification of species and recording of ecological observations. With funding from the National Science Foundation, this project is the result of the collaborative efforts between the Departments of Computer Science and Biology at the University of Massachusetts Boston. EFG queries the Integrated Taxonomic Information System (ITIS). On each query we only get a very small part of the ITIS database. XML can describe exactly what data it is delivering, and thus is the preferred format for the query results.

There are three ITIS branches: Canadian, Mexican, and U.S. U.S. ITIS provides the whole data in bulk (about 85MB), but, like many other organizations, it has been slow to join the modern trend to answer queries on it in XML format. Canadian ITIS provides query results in XML, but due to networking delays and some other problems, getting the result takes a long time. We decided to bring the whole database home and return the results of queries in XML format. We are using an RDBMS to store the bulk data. In the following paragraphs we will explain how to generate XML from this relational data.

SQL/XML
This emerging standard enables us to generate XML fragments from relational data. Oracle and many other relational databases support the following standard SQL/XML functions. We will explain most of the SQL/XML functions, XMLElement, XMLAttributes, XMLForest, XMLAgg, and give an example for each.

XMLElement
As its name implies, this function generates an XML element. It takes an element name, an optional collection of attributes for the element, and zero or more arguments that make up the element content and returns an instance of type XMLType.

Table 1 is the schema of the "experts" table from our database. All the simple examples are related to this table, and Oracle was used for most cases.

Table 2 shows some of the data from the experts table.

in ORACLE:
SQL> SELECT
XMLELEMENT("name", expert)
FROM experts
WHERE expert_id BETWEEN 4 and 10;

in DB2:
SELECT
XML2CLOB( XMLELEMENT(NAME "name", expert) )
FROM experts
WHERE expert_id BETWEEN 4 and 10

XMLELEMENT("NAME",EXPERT)
--------------------------------------
<name>Stotler, Raymond E.</name>
<name>Alfred L. Gardner</name>
<name>Steve J. Upton</name>
<name>Wayne Starnes</name>
<name>Lynne Parenti</name>

Here, name is the tag name; expert is the corresponding column name in the experts table. This query obtains the expert column value from the experts table and puts <name> and </name> tags around it.

XMLAttributes
This function simply creates attributes for an element. Listing 1 shows how it works.

Each name element has an id attribute. Each id attribute is obtained from the expert_id column value of the experts table. The XMLAttributes clause isn't used alone; it's used inside an XMLElement function. This makes sense since, in XML, an attribute is a name-value pair attached to the associated element's start tag.

XMLForest
The XMLForest() function produces a forest of XML elements from the given list of arguments. In other words, it produces many XML elements at a time.

In Listing 2, expert is the root element. The id, name, and info elements are children of expert element. The XMLForest function created three elements: the id element corresponds to expert_id column value; the name element corresponds to expert column value; and the info element corresponds to the exp_comment column value in experts table. Naming the elements in this function is accomplished by using AS keyword. For the first element it is expert_id as "id".

What if you don't specify an id (id = 6 in this example)? In this case you will have the appropriate XML fragment for each row.

XMLAgg
XMLAgg() is an aggregate function that produces a forest of XML elements derived from a set of rows.

In Listing 3, there are five name elements as children of the experts root element. Without using XMLAgg() function it's impossible to have many name elements in a single XML document. If you don't use the XMLAgg() function and instead submit the query, you will get Listing 4.

That's not what we wanted. For this output, each experts element has only one name child element, and there are many experts elements. To combine information from multiple rows of the table we need to use the XMLAgg() function.

Creating hierarchical data is easy. Listing 5 shows you how to do this. You can use this idea in similar queries. This example involves the taxonomic_units table, which stores data about species, phyla, etc., i.e. nodes in the tree of life. Note the select within the select, to run through the inner loop.

I hope these examples have convinced you that it's possible to generate XML that obeys any XML Schema or DTD. You can use these functions, nested in each other, to display any kind of parent-child relation. Another such query could display all the experts for each taxon.

This is easy! You can start using these functions as soon as you finish reading this article. Being flexible adds value to this approach. An important point that's worth mentioning is that all the work is done inside the database by using the SQL/XML functions. In the Oracle case, all the work is done in the XML DB Engine. This is much faster and more efficient than doing the work outside the database. Another approach would be to get the data from the database and tag it outside the database for creating XML. This is possible but less efficient, more time consuming. The following are more advanced topics, such as XMLType View and XML Schema validation.

XMLType View
A view is a table that results from a subquery, but which has its own name and can be treated in most ways as if it were an ordinary table. Thus a view table is a logical window on selected data from the base tables and other views. XMLType views wrap the relational data in XML formats. In other words you can have a virtual XML file over your relational data inside the database. We can treat this new view as XML, and use XML specific operations on it, such as XPath and XQuery. These types of operations will be converted to corresponding SQL; this is known as query rewrite. You can create this view by using SQL/XML functions. Our SQL, which generates an XMLType view is shown in the source code, create.sql, online at www.syscon.com/xmlj/sourcec.cfm. Below is a simpler example, for better understanding.


SQL>  CREATE OR REPLACE VIEW expert_view of XMLTYPE WITH OBJECT ID
(EXTRACT(sys_nc_rowinfo$,[email protected]').getnumberval()) AS 
SELECT XMLELEMENT("experts",
XMLAGG( XMLELEMENT("expert", XMLATTRIBUTES(expert_id as "id"), expert)))
  	 FROM experts
WHERE expert_id BETWEEN 4 and 10;

View created.

This is how the view looks when you query it. This XMLType view is created over the experts table. The data displayed comes from the underlying relational table.


SQL> SELECT * FROM expert_view;

SYS_NC_ROWINFO$

<experts>
  <expert id="4">Stotler, Raymond E.</expert>
  <expert id="5">Alfred L. Gardner</expert>
  <expert id="6">Steve J. Upton</expert>
  <expert id="8">Wayne Starnes</expert>
  <expert id="10">Lynne Parenti</expert>
</experts>

It's possible to use XPath expressions on this view. Following is a query that has an XPath expression. This query extracts the value of an expert element that has an id attribute equal to 4.

SQL> SELECT extract(value(x), '/experts/expert[@id=4]') FROM expert_view x;
EXTRACT(VALUE(X),'/EXPERTS/EXPERT[@ID=4]')

<expert id="4">Stotler, Raymond E.</expert>

First Step: Register an XSD
You can validate an XMLType instance against XML Schema Documentation (XSD) in Oracle. First, register the XSD in Oracle. Next, call a function that does the validation. There are several functions in Oracle for accomplishing this. Listing 6 is the only one shown, for simplicity.

This will register the XSD and name it as expert_view.xsd. You can name it anything you want of course.

Second Step: Validation
This section introduces isSchemaValid (argumet1, argument2). The first argument is the name of the registered XSD. The second argument is the name of the root element in the specified XSD. XSD can have more than one root. If the XML document (x in this case) is valid, this isSchemaValid returns 1.

SQL> select x.isSchemaValid('expert_view.xsd', 'experts') from expert_view x;

X.ISSCHEMAVALID('EXPERT_VIEW.XSD','EXPERTS')

1

Putting It Together
For our project we generated XML that obeys our XML Schema Documentation (XSD)[itis.xsd]. Our SQL is shown in the source code for this article (available online at www.sys-con.com/xmlj/sourcec.cfm). It will output all the data in XML. As you can see, this query is significantly nested and long, but it's easy to understand (I hope). There are a few more functions in this query that we would like to mention: trim() simply gets rid of the white space in an entry and the to_char() function is used to change the date format. For example, to_char(v.up date_date, 'YYYY-MM-DD') will display the date as something like 2004-04-23. This is somewhat important because the date format in the database doesn't match the date format of the XML Schema.

XML Delivery
In this project we deliver XML through a search server. Using this server, you can query the database by tsn, which is identical to the identification number of a taxonomic unit. e.g. a particular animal or plant. For this part of the project we used:

  1. Sun Enterprise 250 Server: 2 X UltraSPARC-II 400MHz, 2GB memory, SCSI disks
  2. Sun Solaris Operating System 5.8
  3. Oracle 9.2i
  4. JAVA 1.4.2
  5. Java Database Connectivity (JDBC) inside JavaBeans for connecting to and querying the database. We preferred the Oracle thin driver to the oci driver.
  6. JavaServer Pages (JSP) at the user interface level
  7. Tomcat 5.0.12
JSP gets a request from the user, and passes it to a JavaBean. The JavaBean connects to Oracle using JDBC, gets information from the database, and passes it back to the JSP. For better performance:
  • Use Oracle Connection Pool
  • Turn off the auto commit feature
  • Use predefined column types in the select statement.
Conclusion
If you are not already doing so, you will probably be delivering your data in XML format soon. Keep in mind that most data resides in relational databases so generating XML from relational databases will be a very common task. Many of the database vendors, such as Oracle and DB2, provide the SQL/XML function support to make this task easier. SQL Server 2000 adds a new clause, the FOR XML clause, to the SELECT statement, which instructs SQL Server to return the result of a query in XML format.

Based on our experiments, generating XML data inside the database is fast; it took an average of 0.032 seconds per taxon query. Storing your data without the start and end tags is space efficient. Relational databases are very mature about storing, querying, and retrieving data quickly and efficiently. There has been over 20 years of work done in these technologies.

For larger projects, having XMLType views over the relational tables is advantageous. It gives you the chance to query the data using XPath and XQuery besides SQL.

In this article we provided some insight about SQL/XML functions, mentioned the benefits of storing data in relational databases, and explored the benefits of using XMLType views over relational data. For more detailed information about SQL/XML functions, check your database documentation.

References

  • O'Neil, Patrick and Elizabeth. (2000) Database:Principles, Programming, Performance. Morgan Kauffman.
  • and "Electronic Field Guide: An Object-Oriented WWW Database to Identify Species and Record Ecological Observations" www.cs.umb.edu/efg
  • Oracle9i Database Online Documentation http://otn.oracle.com/pls/db92/db92.homepage
  • Appelquist, Daniel K. (2001). XML and SQL: Developing Web Applications. Addison-Wesley.
  • Katz, Howard; and Chamberlin, D.D. (2003) XQuery from the Experts: A Guide to the W3C XML Query Language. Addison-Wesley.
  • More Stories By Selim Mimaroglu

    Selim Mimaroglu is a PhD candidate in computer science at the University of Massachusetts in Boston. He holds an MS in computer science from that school and has a BS in electrical engineering.

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


    @ThingsExpo Stories
    The 20th International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held June 6-8, 2017, at the Javits Center in New York City, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Containers, Microservices and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportunity. Submit your speaking proposal ...
    In this strange new world where more and more power is drawn from business technology, companies are effectively straddling two paths on the road to innovation and transformation into digital enterprises. The first path is the heritage trail – with “legacy” technology forming the background. Here, extant technologies are transformed by core IT teams to provide more API-driven approaches. Legacy systems can restrict companies that are transitioning into digital enterprises. To truly become a lead...
    The Internet of Things (IoT) promises to simplify and streamline our lives by automating routine tasks that distract us from our goals. This promise is based on the ubiquitous deployment of smart, connected devices that link everything from industrial control systems to automobiles to refrigerators. Unfortunately, comparatively few of the devices currently deployed have been developed with an eye toward security, and as the DDoS attacks of late October 2016 have demonstrated, this oversight can ...
    You have great SaaS business app ideas. You want to turn your idea quickly into a functional and engaging proof of concept. You need to be able to modify it to meet customers' needs, and you need to deliver a complete and secure SaaS application. How could you achieve all the above and yet avoid unforeseen IT requirements that add unnecessary cost and complexity? You also want your app to be responsive in any device at any time. In his session at 19th Cloud Expo, Mark Allen, General Manager of...
    Bert Loomis was a visionary. This general session will highlight how Bert Loomis and people like him inspire us to build great things with small inventions. In their general session at 19th Cloud Expo, Harold Hannon, Architect at IBM Bluemix, and Michael O'Neill, Strategic Business Development at Nvidia, discussed the accelerating pace of AI development and how IBM Cloud and NVIDIA are partnering to bring AI capabilities to "every day," on-demand. They also reviewed two "free infrastructure" pr...
    As data explodes in quantity, importance and from new sources, the need for managing and protecting data residing across physical, virtual, and cloud environments grow with it. Managing data includes protecting it, indexing and classifying it for true, long-term management, compliance and E-Discovery. Commvault can ensure this with a single pane of glass solution – whether in a private cloud, a Service Provider delivered public cloud or a hybrid cloud environment – across the heterogeneous enter...
    "Dice has been around for the last 20 years. We have been helping tech professionals find new jobs and career opportunities," explained Manish Dixit, VP of Product and Engineering at Dice, in this SYS-CON.tv interview at 19th Cloud Expo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
    Extracting business value from Internet of Things (IoT) data doesn’t happen overnight. There are several requirements that must be satisfied, including IoT device enablement, data analysis, real-time detection of complex events and automated orchestration of actions. Unfortunately, too many companies fall short in achieving their business goals by implementing incomplete solutions or not focusing on tangible use cases. In his general session at @ThingsExpo, Dave McCarthy, Director of Products...
    "ReadyTalk is an audio and web video conferencing provider. We've really come to embrace WebRTC as the platform for our future of technology," explained Dan Cunningham, CTO of ReadyTalk, in this SYS-CON.tv interview at WebRTC Summit at 19th Cloud Expo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
    The many IoT deployments around the world are busy integrating smart devices and sensors into their enterprise IT infrastructures. Yet all of this technology – and there are an amazing number of choices – is of no use without the software to gather, communicate, and analyze the new data flows. Without software, there is no IT. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, Dave McCarthy, Director of Products at Bsquare Corporation; Alan Williamson, Principal...
    Businesses and business units of all sizes can benefit from cloud computing, but many don't want the cost, performance and security concerns of public cloud nor the complexity of building their own private clouds. Today, some cloud vendors are using artificial intelligence (AI) to simplify cloud deployment and management. In his session at 20th Cloud Expo, Ajay Gulati, Co-founder and CEO of ZeroStack, will discuss how AI can simplify cloud operations. He will cover the following topics: why clou...
    Video experiences should be unique and exciting! But that doesn’t mean you need to patch all the pieces yourself. Users demand rich and engaging experiences and new ways to connect with you. But creating robust video applications at scale can be complicated, time-consuming and expensive. In his session at @ThingsExpo, Zohar Babin, Vice President of Platform, Ecosystem and Community at Kaltura, discussed how VPaaS enables you to move fast, creating scalable video experiences that reach your aud...
    WebRTC is the future of browser-to-browser communications, and continues to make inroads into the traditional, difficult, plug-in web communications world. The 6th WebRTC Summit continues our tradition of delivering the latest and greatest presentations within the world of WebRTC. Topics include voice calling, video chat, P2P file sharing, and use cases that have already leveraged the power and convenience of WebRTC.
    "At ROHA we develop an app called Catcha. It was developed after we spent a year meeting with, talking to, interacting with senior citizens watching them use their smartphones and talking to them about how they use their smartphones so we could get to know their smartphone behavior," explained Dave Woods, Chief Innovation Officer at ROHA, in this SYS-CON.tv interview at 19th Cloud Expo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
    20th Cloud Expo, taking place June 6-8, 2017, at the Javits Center in New York City, NY, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy.
    In his keynote at 18th Cloud Expo, Andrew Keys, Co-Founder of ConsenSys Enterprise, provided an overview of the evolution of the Internet and the Database and the future of their combination – the Blockchain. Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life sett...
    DevOps is being widely accepted (if not fully adopted) as essential in enterprise IT. But as Enterprise DevOps gains maturity, expands scope, and increases velocity, the need for data-driven decisions across teams becomes more acute. DevOps teams in any modern business must wrangle the ‘digital exhaust’ from the delivery toolchain, "pervasive" and "cognitive" computing, APIs and services, mobile devices and applications, the Internet of Things, and now even blockchain. In this power panel at @...
    The Internet of Things will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform and how we integrate our thinking to solve complicated problems. In his session at 19th Cloud Expo, Craig Sproule, CEO of Metavine, demonstrated how to move beyond today's coding paradigm and sh...
    With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place June 6-8, 2017, at the Javits Center in New York City, New York, is co-located with 20th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry p...
    20th Cloud Expo, taking place June 6-8, 2017, at the Javits Center in New York City, NY, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy.