Welcome!

Industrial IoT Authors: Elizabeth White, Stackify Blog, Yeshim Deniz, SmartBear Blog, Liz McMillan

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$,'/experts/expert/@id').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
    DX World EXPO, LLC, a Lighthouse Point, Florida-based startup trade show producer and the creator of "DXWorldEXPO® - Digital Transformation Conference & Expo" has announced its executive management team. The team is headed by Levent Selamoglu, who has been named CEO. "Now is the time for a truly global DX event, to bring together the leading minds from the technology world in a conversation about Digital Transformation," he said in making the announcement.
    "Space Monkey by Vivent Smart Home is a product that is a distributed cloud-based edge storage network. Vivent Smart Home, our parent company, is a smart home provider that places a lot of hard drives across homes in North America," explained JT Olds, Director of Engineering, and Brandon Crowfeather, Product Manager, at Vivint Smart Home, in this SYS-CON.tv interview at @ThingsExpo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    SYS-CON Events announced today that Conference Guru has been named “Media Sponsor” of the 22nd International Cloud Expo, which will take place on June 5-7, 2018, at the Javits Center in New York, NY. A valuable conference experience generates new contacts, sales leads, potential strategic partners and potential investors; helps gather competitive intelligence and even provides inspiration for new products and services. Conference Guru works with conference organizers to pass great deals to gre...
    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. In his session at @ThingsExpo, Craig Sproule, CEO of Metavine, demonstrated how to move beyond today's coding paradigm and shared the must-have mindsets for removing complexity from the develop...
    In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, led attendees through the exciting evolution of the cloud. He looked at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering m...
    "Evatronix provides design services to companies that need to integrate the IoT technology in their products but they don't necessarily have the expertise, knowledge and design team to do so," explained Adam Morawiec, VP of Business Development at Evatronix, in this SYS-CON.tv interview at @ThingsExpo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. In his session at @BigDataExpo, Jack Norris, Senior Vice President, Data and Applications at MapR Technologies, reviewed best practices to ...
    Widespread fragmentation is stalling the growth of the IIoT and making it difficult for partners to work together. The number of software platforms, apps, hardware and connectivity standards is creating paralysis among businesses that are afraid of being locked into a solution. EdgeX Foundry is unifying the community around a common IoT edge framework and an ecosystem of interoperable components.
    Large industrial manufacturing organizations are adopting the agile principles of cloud software companies. The industrial manufacturing development process has not scaled over time. Now that design CAD teams are geographically distributed, centralizing their work is key. With large multi-gigabyte projects, outdated tools have stifled industrial team agility, time-to-market milestones, and impacted P&L stakeholders.
    "Akvelon is a software development company and we also provide consultancy services to folks who are looking to scale or accelerate their engineering roadmaps," explained Jeremiah Mothersell, Marketing Manager at Akvelon, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    "IBM is really all in on blockchain. We take a look at sort of the history of blockchain ledger technologies. It started out with bitcoin, Ethereum, and IBM evaluated these particular blockchain technologies and found they were anonymous and permissionless and that many companies were looking for permissioned blockchain," stated René Bostic, Technical VP of the IBM Cloud Unit in North America, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Conventi...
    In his session at 21st Cloud Expo, Carl J. Levine, Senior Technical Evangelist for NS1, will objectively discuss how DNS is used to solve Digital Transformation challenges in large SaaS applications, CDNs, AdTech platforms, and other demanding use cases. Carl J. Levine is the Senior Technical Evangelist for NS1. A veteran of the Internet Infrastructure space, he has over a decade of experience with startups, networking protocols and Internet infrastructure, combined with the unique ability to it...
    22nd International Cloud Expo, taking place June 5-7, 2018, at the Javits Center in New York City, NY, and co-located with the 1st DXWorld Expo 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 ...
    "Cloud Academy is an enterprise training platform for the cloud, specifically public clouds. We offer guided learning experiences on AWS, Azure, Google Cloud and all the surrounding methodologies and technologies that you need to know and your teams need to know in order to leverage the full benefits of the cloud," explained Alex Brower, VP of Marketing at Cloud Academy, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clar...
    Gemini is Yahoo’s native and search advertising platform. To ensure the quality of a complex distributed system that spans multiple products and components and across various desktop websites and mobile app and web experiences – both Yahoo owned and operated and third-party syndication (supply), with complex interaction with more than a billion users and numerous advertisers globally (demand) – it becomes imperative to automate a set of end-to-end tests 24x7 to detect bugs and regression. In th...
    "MobiDev is a software development company and we do complex, custom software development for everybody from entrepreneurs to large enterprises," explained Alan Winters, U.S. Head of Business Development at MobiDev, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection between Coke and its customers. Digital signs pair software with high-resolution displays so that a message can be changed instantly based on what the operator wants to communicate or sell. In their Day 3 Keynote at 21st Cloud Expo, Greg Chambers, Global Group Director, Digital Innovation, Coca-Cola, and Vidya Nagarajan, a Senior Product Manager at Google, discussed how from store operations and ...
    "There's plenty of bandwidth out there but it's never in the right place. So what Cedexis does is uses data to work out the best pathways to get data from the origin to the person who wants to get it," explained Simon Jones, Evangelist and Head of Marketing at Cedexis, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5–7, 2018, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buye...
    SYS-CON Events announced today that Telecom Reseller has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5-7, 2018, at the Javits Center in New York, NY. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.