Posts

Creating XML from Relational Databases


Sometimes following an example someone else created is a good way to get a quick start on a project. The downside is you might miss a better, more efficient solution. In our recent post on XML in the Cloud, we used DatabaseSpy to connect to a local MySQL database and to the Amazon Relational Database Service in the cloud. We used the Concat( ) function in a SQL SELECT statement to create XML formatted output from non-XML data as shown below. DatabaseSpy SQL query and result Our SELECT statement was based on an example in the MySQL documentation on XML support. Let’s take a little deeper look at the problem this statement tries to address. You can copy a DatabaseSpy Results table like the one displayed above and paste it into an editing window in XMLSpy, but the Results table alone does not create a well-formed XML document. To be well formed according to the W3C definition, an XML document must contain a root element. All other elements and logical structures must nest within the root. You can also think of the root element as a wrapper around the entire XML content, the same way the element <city></city> encloses each line in our original results. A Better Way to Create XML from Relational Data We don’t need to manually edit the results to add a root element, nor do we need to adapt our already-complicated SQL query to add the root. DatabaseSpy lets us easily export well-formed XML documents from database tables that contain ordinary data like our cities table. In the DatabaseSpy Export dialog we can choose XML Structure as the output format, click the cities table to select it from the database hierarchy, and choose XMLSpy as the destination. The Preview section at the bottom of the Export dialog shows a view into the table contents. DatabaseSpy Export Dialog When we click the Export button, DatabaseSpy formats the relational data with XML element names derived from the column names of the table and sends the resulting output directly to XMLSpy. The screenshot below shows a portion of the file in XMLSpy. The Message window at the bottom verifies the file is well formed. XMLSpy Editing window and Message window Note that DatabaseSpy supplied the root element <Import name = “cities”> and added comments to describe the datatypes of the database table columns. And, we did not have to construct a SQL statement with a cumbersome Concat( ) function. We began this post to address the simple requirement for a root element to complete the output of the Concat ( ) function we described earlier. When real-world projects require converting from relational databases to XML, the requirements are likely to be much more complex. Altova XMLSpy connects directly to all popular databases to work with XML technologies and relational data. XMLSpy lets you easily create an XML Schema from a database structure, or create a database schema from an XML Schema. XMLSpy also includes advanced editors and debuggers for XQuery and XPath for XML stored directly in databases, along with specialized support for XML features in Microsoft SQL Server, IBM DB2, and Oracle databases. As more industries adopt and evolve XML-based standards for information interchange, a common need is to convert data stored in legacy databases to XML. Altova MapForce connects to databases and allows you to map and transform relational data to be compatible with one or more XML Schemas. You can use your mapping to perform a one-time data conversion, you can save and re-open your mapping to perform another conversion later, or you can instruct MapForce to generate royalty-free source code from your mapping to include in your own project when repeated conversions are required. If you’d like to see for yourself how well Altova tools can generate well-formatted XML from relational databases, download a free trial of the Altova MissionKit.

Tags: , , , , ,

XML in the Cloud


Working with Altova Tools and the Amazon Relational Database Service (Amazon RDS)

More and more enterprises are discovering the advantages of implementing database applications in the cloud:

  • High availability and reliability
  • Automatic scaling
  • Freedom from hardware costs and maintenance requirements

In this blog post we demonstrate how to connect to the Amazon Relational Database Service (Amazon RDS) and build a small database using Altova DatabaseSpy. Since the database Connection Wizard is consistent across the Altova MissionKit, you can connect the same way using XMLSpy, MapForce, or StyleVision. If you would like to follow the steps described below for yourself, you will need to sign up for an Amazon Web Services (AWS) account at: http://aws.amazon.com/rds/ You can also download a fully-functional free trial of the Altova MissionKit or any individual Altova application at: https://www.altova.com/download-trial/

Build a Local Prototype

The Amazon RDS is based on MySQL, so we will build a small local database in the MySQL Community Edition, then migrate to the Amazon RDS and test our database in the cloud. Although MySQL does not support XML as a data type for database columns, MySQL 5.1 and 6.0 do support some operations for XML data stored as text. For this exercise we will adapt and extend some of the MySQL XML examples at the MySQL reference resources listed here: http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html First, we launched DatabaseSpy and connected to our local MySQL Community Edition. We created a new data source named LocalPrototype, and created a new database schema that we named XMLtest. The DatabaseSpy Online Browser and Properties windows are shown here: DatabaseSpy Project and Properties windows Next, we created two tables called books and cities and inserted data by following the examples in the MySQL documentation. Here is a DatabaseSpy Design View of our tables: DatabaseSpy Design View We can run select queries and display the contents of our tables in stacked results windows: DatabaseSpy stacked results windows Note that the doc column of the books table contains XML data, although it was defined as varchar(150). MySQL supports two functions for working with XML in text fields, ExtractValue() and UpdateXML() that can operate on individual elements via XPath expressions. Below is a simple ExtractValue() query to return only the author initials from every row in the books table: ExtractValue( ) function The UpdateXML() function can be used to modify the contents of individual XML elements using a SQL expression. In the screen shot below, the query on line 1 updates the every row of our books table, and the query on line 2 returns the new values: UpdateXML( ) function We can also use the Concat( ) function to add XML elements to non-XML data such as the cities table, as shown below: Concat( ) function So far, our XML queries have operated on all rows of each table. To facilitate queries for a single row, it’s handy to add a column top the table to hold a unique row index. We can make a copy of our books table and add a column called id to hold the row index. The id column also makes a convenient foreign key to reference an individual XML document in our table from a row in another table. For instance, you might define one table to contain names of job candidates, with a foreign key to reference the XML-formatted resume for each candidate, stored in a separate table. You can use the SQL Editor in DatabaseSpy to generate a CREATE statement for the existing books table and edit it directly, or you can use the DatabaseSpy Design Editor to build the table graphically. (For more information, see the DatabaseSpy section of the Altova Web site.) Since we are planning to run the same queries later in the Amazon RDS, we combined a SQL CREATE statement and SQL INSERT statements into one script for the books2 table. The screen shot below shows part of the script for books2: Create table script We can run a query of the books2 table that shows the unique id column for each row: SQL SELECT query Now we can enhance our UpdateXML() and ExtractValue() queries to act on an individual row: blogSnap8 blogSnap9 This gives us a good baseline set of examples to take to the cloud and test in an Amazon RDS.

Connect DatabaseSpy to the Amazon RDS Cloud

After you follow the instructions at the AWS Management Console to create a database instance on Amazon RDS, the Connection Wizard makes it easy to get started with DatabaseSpy. Simply choose the MySQL option as shown here: DatabaseSpy Connection Wizard The first time you connect, you will need to create a new DSN. After the first time, you will be able to select the DSN from a list by choosing the “Use an existing Data Source Name” option. You can even use the original DSN when you go back to connect from XMLSpy, MapForce, or StyleVision. Connecting to MySQL In the connector dialog, fill in the following information:

  • Data Source Name: This is the name that will be listed in the DatabaseSpy Project. window and in the list of existing data sources when you connect again.
  • Description: Information for your own reference.
  • Server: This is the Endpoint name listed in your Amazon RDS account dashboard.
  • Port: 3306 – make sure your IT department isn’t blocking this port with a firewall!
  • User / Password: This is a user you set up in Amazon RDS.
  • Database: The default database name you configured when launching your RDS instance.

MySQL Connector/ODBC We connected to our Amazon RDS cloud database in the same DatabaseSpy project we built for the local prototype. Here is a screen shot of the project window showing both Data Source Names and the working SQL files we added to our project: DatabaseSpy Project window with cloud connection Before we build our tables and run the queries, it will be interesting to check the versions of each system. The screen shots below show a query that requests version information for each system. Note that the gray bar directly above each query indicates which data connection the SQKL statement is assigned to. Version reported by the local server Version reported by the cloud server The Amazon RDS reports it is running version 5.1 of the MySQL Community Server, the same as our local prototype – a promising omen!

Migrate the Local Project to the Cloud

We can open each of our original table creation scripts and run them in the cloud database by re-assigning the execution target  in the Properties window: Data Source selection in the DatabaseSpy Properties window The gray Execution Target bar near the top of the SQL Editor window identifies the cloud Amazon RDS database as the query target: DatabaseSpy SQL Editor window After similarly creating the books and books2 tables, we can run each of the SQL queries in the cloud database. ExtractValue() function for all rows example: ExtractValue( ) function Concat() query to create XML output from non-XML data in a table: Using the Concat( ) function to add XML elements to data from a non-XML table UpdateXML() example for a single row in a table. Using the UpdateXML( ) function in the Amazon RDS cloud ExtractValue() for a single row: blogSnap23

Conclusion

In every test we performed, Amazon RDS behaved exactly like the local MySQL community edition. This behavior it much more efficient for developers to build and test new cloud database applications, or enhancements to existing applications, without incurring the cost of cloud resources for development iterations. We also verified the operation of MySQL XML functions for XML data stored in text columns in the cloud databases. Our XML data was very limited – the text column in our books table was limited to 150 characters. However, MySQL lets you store much larger XML documents in a single column. Every table has a maximum row size of 65,535 bytes. Even if your table uses an index column, this means a varchar column for one XML entry could be over 64k bytes. If you need to store even larger XML documents, MySQL offers MediumText and LongText data types, similar to BLOBs. MediumText can hold over 16 million single-byte characters and LongText can hold up to 4 GB. Although not illustrated in this blog post, we have successfully tested ExtractValue() and UpdateXML() functions with MediumText and LongText data types. When you need to store XML data files that large, writing XPath expressions to resolve individual elements can become a development challenge. The XPath Analyzer included with XMLSpy is an invaluable tool that facilitates the testing and debugging of XPath 1.0 and 2.0 expressions. As you type an XPath expression into the analyzer, XMLSpy evaluates it and returns the resulting node set in real time. This can save hours of debugging time spent trying to understand and track down XPath problems. In future blog posts we’ll explore other ways XMLSpy, MapForce, DiffDog, and DatabaseSpy can help developers accelerate creation of cloud application with Amazon RDS. We look forward to seeing you back soon! If you’d like to see for yourself how well Altova tools work with Amazon RDS, download a free trial of the Altova MissionKit.

Tags: , ,

SOA and Cloud Services Within Your Budget


The hardships affecting today’s economy present new challenges for organizations. Interdepartmental budgets are being cut, and large purchases are being carefully scrutinized. Costly enterprise software and mainframe computing systems that once held promise are being reconsidered on a global scale in favor of more agile, component-based systems that cut costs and increase efficiency with forward-thinking concepts like Service-oriented Architecture (SOA) and cloud computing. These architectural concepts incorporate modern technologies and object-oriented approaches to solve real-world technology issues in complex environments while decreasing maintenance, integration, and deployment costs with modular design and component re-use. The Altova MissionKit is a highly affordable toolset uniquely suited to address this shift toward more flexible and lightweight infrastructure. With strong support for XML, UML, databases, and data integration technologies, the MissionKit offers all of the tools necessary to build agile architectures replete with repeatable services, reusable components, and scalable resources.

SOA & Cloud Computing

SOA and Web/cloud services are two of the strongest buzzwords in technology today. Though they have some clear differences, both of these concepts represent a paradigm shift from large-scale enterprise systems to service-based architectures built on modular components and reusable functionality. The SOA approach aims to help organizations respond more quickly to business requirements by packaging processes as a network of interoperable and repeatable services. This modularity creates system flexibility and gives developers the agility required to build new capabilities into the current system as needed – without reinventing the proverbial wheel. SOA is essentially a series of interconnected and self-contained services, the functionality of which is dynamically located and invoked based on certain criteria, communicated in messages. At the heart of SOA is a high level of component reuse that drives down costs and increases efficiency in a fully scalable architecture. Cloud services build upon the concept of interoperable services, adding a virtualization component to help relieve internal servers from being overtaxed by the constant reuse of these services within the system. This paradigm uses the Internet and Internet-enabled technologies to increase performance and processing speed by storing information permanently in the "cloud" and caching it only temporarily on client machines. Cloud computing implementation is a powerful option for increasing system capacity and capabilities by leveraging next-generation data centers in combination with the World Wide Web. Both SOA and cloud computing seek to alleviate problems created by inflexible architectures that rely heavily on tightly coupled enterprise application infrastructure. This focus on interoperability and independent software services reveals a distributed solution that is event-driven, flexible, and cost conscious in almost any setting.

Anatomy of a Service-based Architecture

Since their inception, XML and Web services have been continuously gaining notoriety as the standards of choice for secure, efficient, and platform-independent data exchange between software applications and over the Internet. XML provides the foundation for the protocols that power Web services infrastructure: WSDL (Web Services Description Language) and SOAP, an XML-based messaging standard. Web services are hardware, programming language, and operating system independent, meaning that they are duly amenable to the seamless and interoperable exchange of data over a network and uniquely suited to component-based systems. Web services architecture Web services architecture Both SOA and cloud-based architectures generally rely on WSDL to describe interaction and functionality and locate operating components within the system. WSDL works hand-in-hand with SOAP, a messaging protocol used by the client application to invoke the methods and functions defined in the WSDL description. The example below is the stock quote example used in the W3C WSDL specification and describes a simple, single operation service that retrieves real-time stock prices based on ticker symbol input. Of course, most services that exist within enterprise architectures are far more complex. Graphical WSDL editor Take, for example, the publicly available Amazon Web services, which provide accessible Cloud services and infrastructure to a growing number of companies worldwide, including Twitter, SmugMug, and WordPress.com. These services essentially allow independent organizations to rent some of the immense power built into the Amazon distributed computing environment and add the same scalability, reliability, and scalability to their online presence at a fraction of the price. The much anticipated Windows Azure from Microsoft® operates on a similar model, giving developers the opportunity to build and deploy cloud-based applications with minimal on-site resources. Amazon provides a WSDL file that contains the definition of the Web service, the requests that the service accepts, and so on. Developers can then write a SOAP-based client application that invokes the Amazon Web service for the functionality it provides. (At this time Amazon provides a number of Cloud-based services for application hosting, backup and storage, content delivery, e-commerce, search, and high-performance computing.)

Altova MissionKit

Recently named "Best Development Environment" in the Jolt Product Excellence Awards, the Altova MissionKit is a diverse set of software tools that provides scalable options for leveraging your current software assets in an SOA or cloud-enabled environment. Strong support for XML, Web services, data integration, process automation, and databases, as well as accessibility to powerful APIs give developers flexible options for creating service-based solutions and an affordable alternative to costly consultant fees, extract/transform/load (ETL) tools, and/or enterprise service bus (ESB) products. The Altova MissionKit* supports end-to-end Web services development and includes a graphical WSDL editor, visual Web services builder, advanced capabilities for managing WSDL and other XML file relationships, a SOAP client and debugger, WSDL data integration, code generation, and more. Together, all of these features provide a robust solution for integrating disparate services and systems in a distributed computing environment, whether the components be in-house, network, or Cloud-based.

WSDL Editor

The XMLSpy XML editor provides a graphical interface (GUI) for designing and editing WSDL documents. The structure and components of the WSDL are created in the main design window using graphical design mechanisms (with tabs allowing users to toggle back and forth between text view), and additional editing capabilities are enabled from comprehensive entry helper windows. Users can easily create and edit messages, types, operations, portTypes, bindings, etc., inline. In addition, publicly maintained WSDL files like the Amazon Simple Storage Service, or Amazon S3, (below) can be opened instantly using the Open URL command in XMLSpy. WSDL editor Amazon Web services XMLSpy’s WSDL editor gives developers a sophisticated environment for rapid Web services development, managing WSDL syntax and validation through an intuitive, drag and drop graphical interface. The addition of a documentation generation feature makes it possible to share the complete details of a Web service interface with non-technical stakeholders in HTML or Microsoft Word.

SOAP Client

SOAP requests can be manually created in XMLSpy’s SOAP client based on the operations defined in the WSDL. Once an operation is selected, XMLSpy initiates the request based on the connections provided in the WSDL and displays the XML syntax of the SOAP envelope in the main window. The message can then be sent directly to the server for an immediate response. SOAP client for Web services

SOAP Debugger

XMLSpy also includes a SOAP debugger, which acts as Web services proxy between client and server, enabling developers to analyze WSDL files and their SOAP message components, single-step through transactions, set breakpoints on SOAP functions, and even define conditional breakpoints that are triggered by a stated XPath query. SOAP debugger

Building Web Services

Once a WSDL definition is complete, it can also be visually implemented using MapForce, Altova’s any-to-any data integration tool. MapForce gives users the ability to map data to or from WSDL operations and then autogenerate program code in Java or C#. Tight integration with Visual Studio and Eclipse makes it possible to then compile the code within either of these IDEs and deploy the service on the client machine. When you create a new Web service project by specifying a Web services definition file (WSDL), MapForce automatically generates mapping files for each individual SOAP operation. MapForce project The SOAP input and output messages can then be easily mapped to other source data components (XML, databases, flat files, EDI, XBRL, Excel 2007) to create a complete Web services operation. Data processing functions, filters, and constants can also be inserted to convert the data on the fly. Web services mapping MapForce can autogenerate Web services implementation code in Java or C# for server-side implementation, and it is also accessible for automation via the command line.

File Relationship Management

For complex Web-based applications that include a large number of disparate files and project stakeholders, the MissionKit offers an advanced graphical XML file relationship management tool in SchemaAgent. SchemaAgent can analyze and manage relationships among XML Schemas, XML instance documents (SOAP), WSDL, and XSLT files. The client/server option enables any changes to be visualized in real time across a workgroup. Managing XML files This gives organizations the ability to track and manage their mission critical SOA files as reusable individual components, reducing development time and the occurrence of errors.

Data Integration

A key factor of any SOA is the ability for disparate systems to communicate seamlessly via automated processes. As an any-to-any graphical data integration and Web services implementation tool, MapForce facilitates this undertaking with support for a wide variety of data formats including XML, databases, flat files (which can be easily parsed for integration with legacy systems with the help of the unique FlexText™ utility), EDI, XBRL, Excel 2007, and Web services. MapForce data mapping in Visual Studio MapForce supports complex data mapping scenarios with multiple sources and targets and advanced data processing functions. Transformations can easily be automated via code generation in C#, C++, or Java, or the command line. Full integration with Visual Studio and Eclipse also makes this an ideal development tool for working in large-scale enterprise projects – without the heavy price tag. This gives developers a flexible and agile middleware component that can work in virtually any service-based architecture. The ability to integrate disparate data in on-the-fly is a key requirement in real-world enterprise and cross-enterprise systems where legacy systems and other less flexible formats co-exist with XML and other modern, interoperable standards.

Database Management

Even in the rapidly evolving semantics-driven macrocosm that is Web 2.0, most companies still use one or more relational databases to store and manage their internal data assets. The Altova MissionKit supports working with the most prevalent of these systems (see listing below) in a wide variety of different ways. Database support is offered in XMLSpy, MapForce, StyleVision, and, of course, DatabaseSpy.

  • Microsoft® SQL Server® 2000, 2005, 2008
  • IBM DB2® 8, 9
  • IBM DB2 for iSeries® v5.4
  • IBM DB2 for zSeries® 8, 9
  • Oracle® 9i, 10g, 11g
  • Sybase® 12
  • MySQL® 4, 5
  • PostgreSQL 8
  • Microsoft Access™ 2003, 2007

DatabaseSpy is a multi-database query, editing, design, and comparison tool that allows users to connect directly to all major databases and edit data and design structure in a graphical user interface with features like table browsing, data editing, SQL auto-completion entry helpers, visual table design, content diff/merging, and multiple export formats. In a service-based architecture, the ability to compare and merge data directly in its native database format is an enormous asset to developers who need to locate changes, migrate differences, or synchronize versions of database tables across test and live environments. Database tool and SQL editor   As a component of the MissionKit, DatabaseSpy gives disparate groups within organizations the flexibility to work with data from multiple databases in one central interface simultaneously. Whether this data is eventually integrated into other systems or applications or lives permanently in the database, DatabaseSpy provides a simple and flexible solution to managing and maintaining massive data stores.

Single Source Publishing

In today’s world of highly automated data transfer and management, it is still necessary for human readers to ultimately consume the data in some format or other. Of course, the problem that organizations often run into is what format to publish to. XML and single source publishing have revolutionized content management, document exchange, and even multilingual communications by separating content structure from appearance. An XML-based documentation system can greatly reduce costs through facilitating ease of conversion for delivery to many different data formats and types of applications. The single source concept ensures that workflow processes (i.e., conversion, edits, etc.) do not have to be repeated or reworked – that all content in the repository requires only minimal restructuring and promotion before being loaded to respective applications for delivery. Altova StyleVision is a graphical stylesheet design tool that enables users to easily apply single source publishing to XML, XBRL, and database content, without having any affect on the source data. In this way, companies can create reusable template designs for data that can then be rendered automatically in HTML, RTF, PDF, Microsoft Word 2007, and even an Authentic e-Form for immediate publication to any conceivable medium without any process disruption – resulting in the presentation of accurate, consistent, and standardized information in real-time. StyleVision stylesheet designer Single source publishing gives organizations the ability to add a human component to their highly automated data processing workflows, enabling them to view transmission reports at any stage. For example, in a world where compliance management plays such a large role in day to day enterprise operations, StyleVision can be integrated into any SOA to provide a sort of visual audit trail for manually reviewing XML, XBRL, and database transactions. StyleVision’s template-based approach to stylesheet design makes it an ideal addition to a distributed development environment, where repeatable processes are an integral part of the system’s overall efficiency.

Conclusions

Financial downturns can make investing in technology a difficult decision. However, forward-thinking organizations will find that focusing on restructuring the legacy assets they already have in place, automating internal processes, and adding virtualization layer to their application infrastructure can lead to increases in efficiency, speed, and potentially enormous ROI. The Altova MissionKit gives businesses all of the tools that they need to augment their enterprise architecture with iterative, process-driven solutions that will recover costs through the reuse of current assets and the ability to deliver Web-driven automation within and across organizations on a global scale. The MissionKit is a highly affordable solution that offers developers, software architects, and IT users all of the tools they need to build flexible and powerful technology solutions and efficiencies that advance component-based service-oriented infrastructure – without breaking the budget.

Tags: , , , , , , , ,