Turn Your Database Schema into Records By Mike McCune

---

Introduction

webMethods Flows commonly interact with a database to write and read data. Using the WmDB package to perform these actions returns only a generic RecordList named results, however, and not a RecordList conforming to your database table schema.

This article discusses a solution for this potential headache.

---

The Problem Defined

When a WmDB service returns a generic RecordList object, a common developer headache is to build a new Record (or RecordList) into which to map the action's results. For example, if the table Customer has three columns -- ID, Name and Address -- a developer must create a new Record to represent this table schema. The process is a familiar one:

1. Select File > New > Record
2. Create the Strings ID, Name, and Address as elements of the Record

The database query results (using pub.db:query) can now be mapped into the Record created above.

When using a database with a large number of tables and tables with large numbers of columns, this process becomes quite tedious, not to mention time-consuming and error-prone. Small spelling mistakes can lead to bugs which are very difficult to identify.

---

The Solution: Use a Third-Party XML Tool

A 3rd party tool can greatly speed up this process and simplify the process of incorporating database schema changes. XML Spy Enterprise and Pro (http://www.xmlspy.com) offers the ability to use ODBC for connecting to a database and generating an XSD or DTD representation of its schema.

After installing XML Spy, launch it and select Convert > Create Database Schema. Specify the database location, username and password. XML Spy will read the database table structure and build a XSD document. Save the XSD document locally. This document represents the entire database schema -- an element for each table and associated sub-elements for each table's columns.

---

XSD Sample Document

Here is a XSD document for the table Customer mentioned above:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Customers">
<xs:complexType>
<xs:sequence>
<xs:element name="ID">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Name">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Address">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

To create a webMethods Record based on this XSD, create a new Record (File > New > Record) and when prompted for the Record source, choose "XML Schema" and specify the location of the XSD document saved from within XML Spy.

Name the Record for its root node and it will be created as:

Next, select the sub-elements of the Customer node and move them outside of Customer so that they are no longer sub-elements. Next, delete the Customer node. The Record will now resemble the following:

You can now use your newly-created Record for record referencing -- without ever having to manually type column names!

Editor's Note: XML Spy is an Altova product and is offered as a 30 day free trial. Pricing starts at $399 for the Professional Edition and $999 for Enterprise Edition. Download XML Spy at http://www.xmlspy.com

---

[1]  

Go Deeper on the Subject: The wMUsers Discussion Forums