Ask Ben: Converting XML Data To ColdFusion Queries

Posted December 21, 2007 at 10:24 AM

Tags: Ask Ben, ColdFusion

I work at a veterinary hospital and am generating xml from an IBM UniVerse DB which may or may not contain multi-valued data, and parsing/displaying it via CF8. I have written custom java classes take the user input from the cfform, handle the db transactions and pass back an xml object which is then parsed in CF. (the multi-value data driver does funky things with the data and causes CF to drop the connection intermittently.)

<MAIN _ID = "1081693">
<LAST_MV LAST = "MALLIK"/>
<FIRST_MV FIRST = "A.K. BOBBY"/>
<AN_NAME_MV AN_NAME = "BOTAR"/>
<AN_NAME_MV AN_NAME = "BONES"/>
<AN_BREED_MV AN_BREED = "MIXB"/>
<AN_BREED_MV AN_BREED = "GERM"/>
<ADDRESS_MV ADDRESS = "555 HAPPY ST"/>
<ADDRESS_MV/>
<CITY_MV CITY = "PHILADELPHIA"/>
<AN_NUM_MV AN_NUM = "647873"/>
<AN_NUM_MV AN_NUM = "645692"/>
</MAIN>
<MAIN _ID = "1173319">
<LAST_MV LAST = "MALLIK"/>
<FIRST_MV FIRST = "JOE"/>
<AN_NAME_MV AN_NAME = "SMOKEY"/>
<AN_BREED_MV AN_BREED = "MIXB"/>
<ADDRESS_MV ADDRESS = "512 SAD ST "/>
<ADDRESS_MV/>
<CITY_MV CITY = "PHILADELPHIA"/>
<AN_NUM_MV AN_NUM = "762268"/>
</MAIN>
</ROOT>

Notice that the first owner has two animals, listing the name, breed, and animal number (respectively), while the second owner has one. I am having the worst time building a query object from this xml. We don't know how many animals a given owner will have. The query object may repeat the owner info, which is fine, as the "key" needs to be the animal number. Any tips would be extremely helpful and greatly appreciated. Thank you.

You could move all this data into one query, but I am not sure if that would help you all that much. While you can build queries manually, any way you want, we should probably try to stick to "better practices" when it comes to database table creation. That being said, when I see your XML data, I see two different queries: one for customers and one for pets. Doing this gives us the ability to create multiple pets for a single customer and then relating them in a one-to-many relationship without having to unnecessarily duplicate data.

To do this, we are going to create a customer table with the following fields:

  • id
  • last_name
  • first_name
  • street
  • city

Then, we are going to have a pets table with the following fields:

  • id
  • name
  • breed
  • customer_id

Here, it is the "customer_id" column that relates the pet back to the customer table. The customer_id is the foreign key constraint that matches the "id" column of the customer table. Doing this allows you to access the data in a more dynamic and flexible way.

The plan is easy, but getting the XML data into the query is not so easy. It's not too complicated, it just take a lot of foot work and elbow grease. We're going to be using XPath() to search for nodes and then take that node data and populating the various queries. The tricky thing about working with XML data (especially for ME working in an unknown system) is that we can't always be sure WHAT data will always exist. While not all of this is up in the air, since I have never worked with this system or with an IBM UniVerse Database, I am putting potentially excessive "existence checking" for many of the nodes and attribute values. If you know that these nodes will always be there, then you can take a lot of the CFIF logic out.

That being said, here is my solution:

 Launch code in new window » Download code as text file »

  • <!---
  • Create XML document that would be parsed from the
  • data returned by the IBM UniVerse DB. NOTE: No animals
  • were harmed in the parsing of this XML.
  • --->
  • <cfxml variable="xmlData">
  •  
  • <ROOT>
  • <MAIN _ID = "1081693">
  • <LAST_MV LAST = "MALLIK"/>
  • <FIRST_MV FIRST = "A.K. BOBBY"/>
  • <AN_NAME_MV AN_NAME = "BOTAR"/>
  • <AN_NAME_MV AN_NAME = "BONES"/>
  • <AN_BREED_MV AN_BREED = "MIXB"/>
  • <AN_BREED_MV AN_BREED = "GERM"/>
  • <ADDRESS_MV ADDRESS = "555 HAPPY ST"/>
  • <ADDRESS_MV/>
  • <CITY_MV CITY = "PHILADELPHIA"/>
  • <AN_NUM_MV AN_NUM = "647873"/>
  • <AN_NUM_MV AN_NUM = "645692"/>
  • </MAIN>
  • <MAIN _ID = "1173319">
  • <LAST_MV LAST = "MALLIK"/>
  • <FIRST_MV FIRST = "JOE"/>
  • <AN_NAME_MV AN_NAME = "SMOKEY"/>
  • <AN_BREED_MV AN_BREED = "MIXB"/>
  • <ADDRESS_MV ADDRESS = "512 SAD ST "/>
  • <ADDRESS_MV/>
  • <CITY_MV CITY = "PHILADELPHIA"/>
  • <AN_NUM_MV AN_NUM = "762268"/>
  • </MAIN>
  • </ROOT>
  •  
  • </cfxml>
  •  
  •  
  • <!---
  • Create the two queries that we want to work with. One
  • will be the human customer and one will be for the pets
  • that that owner brings to the clinic.
  • --->
  • <cfset qCustomer = QueryNew(
  • "id, last_name, first_name, street, city",
  • "varchar, varchar, varchar, varchar, varchar,"
  • ) />
  •  
  • <!---
  • For the pet query object, be sure to have a customer_id -
  • this is our foreign key to the ID in the qCustomer query.
  • This is how we link multiple pets to one customer.
  • --->
  • <cfset qPet = QueryNew(
  • "id, name, breed, customer_id",
  • "varchar, varchar, varchar, varchar"
  • ) />
  •  
  •  
  • <!---
  • Now that we have our query and our parsed XML document,
  • time to start moving data to the query objects. Let's
  • start off by searching for all MAIN nodes - thse are our
  • customer nodes (remember that Xpath is case sensitive
  • to our upper case nodes names).
  • --->
  • <cfset arrMainNodes = XmlSearch(
  • xmlData,
  • "//MAIN[ @_ID != '' ]"
  • ) />
  •  
  •  
  • <!---
  • For each MAIN (customer) node, we want to create a record
  • in our query. Let's loop over the XML nodes returned in our
  • XPath search.
  • --->
  • <cfloop
  • index="xmlMainNode"
  • array="#arrMainNodes#">
  •  
  • <!--- Create a new record for our customer query. --->
  • <cfset QueryAddRow( qCustomer ) />
  •  
  • <!---
  • Set the customer ID. Remember, when setting values into
  • a Query object, it is important to set the propert Java
  • data type (especially since we are going to later be
  • querying these).
  • --->
  • <cfset qCustomer[ "id" ][ qCustomer.RecordCount ] = JavaCast(
  • "string",
  • xmlMainNode.XmlAttributes._ID
  • ) />
  •  
  •  
  • <!--- Check to see if last name exists. --->
  • <cfif (
  • StructKeyExists( xmlMainNode, "LAST_MV" ) AND
  • StructKeyExists( xmlMainNode.LAST_MV[ 1 ].XmlAttributes, "LAST" )
  • )>
  •  
  • <!--- Set last name. --->
  • <cfset qCustomer[ "last_name" ][ qCustomer.RecordCount ] = JavaCast(
  • "string",
  • xmlMainNode.LAST_MV[ 1 ].XmlAttributes.LAST
  • ) />
  •  
  • </cfif>
  •  
  •  
  • <!--- Check to see if first name exists. --->
  • <cfif (
  • StructKeyExists( xmlMainNode, "FIRST_MV" ) AND
  • StructKeyExists( xmlMainNode.FIRST_MV[ 1 ].XmlAttributes, "FIRST" )
  • )>
  •  
  • <!--- Set last name. --->
  • <cfset qCustomer[ "first_name" ][ qCustomer.RecordCount ] = JavaCast(
  • "string",
  • xmlMainNode.FIRST_MV[ 1 ].XmlAttributes.FIRST
  • ) />
  •  
  • </cfif>
  •  
  •  
  • <!---
  • Check to see if address exists. There might be more
  • than one address node (as you can see in the XML),
  • but for our purposes, we are only going to grab the
  • first one.
  • --->
  • <cfif (
  • StructKeyExists( xmlMainNode, "ADDRESS_MV" ) AND
  • StructKeyExists( xmlMainNode.ADDRESS_MV[ 1 ].XmlAttributes, "ADDRESS" )
  • )>
  •  
  • <!--- Set last name. --->
  • <cfset qCustomer[ "street" ][ qCustomer.RecordCount ] = JavaCast(
  • "string",
  • xmlMainNode.ADDRESS_MV[ 1 ].XmlAttributes.ADDRESS
  • ) />
  •  
  • </cfif>
  •  
  •  
  • <!--- Check to see if city exists. --->
  • <cfif (
  • StructKeyExists( xmlMainNode, "CITY_MV" ) AND
  • StructKeyExists( xmlMainNode.CITY_MV[ 1 ].XmlAttributes, "CITY" )
  • )>
  •  
  • <!--- Set last name. --->
  • <cfset qCustomer[ "city" ][ qCustomer.RecordCount ] = JavaCast(
  • "string",
  • xmlMainNode.CITY_MV[ 1 ].XmlAttributes.CITY
  • ) />
  •  
  • </cfif>
  •  
  •  
  •  
  • <!---
  • ASSERT: At this point, we have fully populated
  • the contact data.
  • --->
  •  
  •  
  • <!---
  • When it comes to populating the pet table, we are
  • really tied to the pet ID. Therefore, we have to
  • go off the number of valid Pet IDs. Query for all
  • pet IDs in this node that have a length.
  • --->
  • <cfset arrPetNodes = XmlSearch(
  • xmlMainNode,
  • "AN_NUM_MV[ @AN_NUM != '' ]"
  • ) />
  •  
  •  
  • <!---
  • Loop over pet ID nodes. We can't use an array loop
  • at this point because we need to know which index
  • of the pet we are viewing within the current customer.
  • --->
  • <cfloop
  • index="intPetIndex"
  • from="1"
  • to="#ArrayLen( arrPetNodes )#"
  • step="1">
  •  
  • <!--- Add a row to the pet table. --->
  • <cfset QueryAddRow( qPet ) />
  •  
  • <!--- Get a short hand to the current pet node. --->
  • <cfset xmlPetNode = arrPetNodes[ intPetIndex ] />
  •  
  •  
  • <!---
  • Set the pet ID. Remember again that is is
  • important to be storing these values using the
  • propery Java types.
  • --->
  • <cfset qPet[ "id" ][ qPet.RecordCount ] = JavaCast(
  • "string",
  • xmlPetNode.XmlAttributes.AN_NUM
  • ) />
  •  
  • <!---
  • Set the contact ID to which this pet will be
  • associated.
  • --->
  • <cfset qPet[ "customer_id" ][ qPet.RecordCount ] = JavaCast(
  • "string",
  • xmlMainNode.XmlAttributes._ID
  • ) />
  •  
  •  
  • <!---
  • Now that we have our pet row in place, we need to
  • check for addition pet data including name and breed.
  • We just have to be careful to ONLY get the node
  • index corresponding to this ID.
  • --->
  •  
  •  
  • <!--- Check for pet name. --->
  • <cfif (
  • StructKeyExists( xmlMainNode, "AN_NAME_MV" ) AND
  • ArrayIsDefined( xmlMainNode.AN_NAME_MV, intPetIndex ) AND
  • StructKeyExists( xmlMainNode.AN_NAME_MV[ intPetIndex ].XmlAttributes, "AN_NAME" )
  • )>
  •  
  • <!--- Set pet name. --->
  • <cfset qPet[ "name" ][ qPet.RecordCount ] = JavaCast(
  • "string",
  • xmlMainNode.AN_NAME_MV[ intPetIndex ].XmlAttributes.AN_NAME
  • ) />
  •  
  • </cfif>
  •  
  •  
  • <!--- Check for pet breed. --->
  • <cfif (
  • StructKeyExists( xmlMainNode, "AN_BREED_MV" ) AND
  • ArrayIsDefined( xmlMainNode.AN_BREED_MV, intPetIndex ) AND
  • StructKeyExists( xmlMainNode.AN_BREED_MV[ intPetIndex ].XmlAttributes, "AN_BREED" )
  • )>
  •  
  • <!--- Set pet name. --->
  • <cfset qPet[ "breed" ][ qPet.RecordCount ] = JavaCast(
  • "string",
  • xmlMainNode.AN_BREED_MV[ intPetIndex ].XmlAttributes.AN_BREED
  • ) />
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • ASSERT: At this point, we have moved all of our XML data
  • into two ColdFusion queries - qCustomer and qPet. These
  • two queries are related through the customer_id columdn.
  • --->
  •  
  •  
  • <!--- Dump out the two tables. --->
  • <cfdump
  • var="#qCustomer#"
  • label="qCustomer Data"
  • />
  •  
  • <cfdump
  • var="#qPet#"
  • label="qPet Data"
  • />

Running the code, you can see that we get our two populated ColdFusion query tables:


  
  
  

 
Pet Clinc XML Data Converted To Two ColdFusion Queries  
 
  
  

Now that we have this data, we can start to access it. You can either query each table individually, or join the two tables to get related information as in this example:

 Launch code in new window » Download code as text file »

  • <!--- Now, we can join the data as needed. --->
  • <cfquery name="qCustomerPet" dbtype="query">
  • SELECT
  • qPet.id,
  • qPet.name,
  • qPet.breed,
  • ( qCustomer.id ) AS customer_id,
  • qCustomer.last_name
  • FROM
  • qCustomer,
  • qPet
  • WHERE
  • qCustomer.id = qPet.customer_id
  • ORDER BY
  • last_name ASC,
  • first_name ASC
  • </cfquery>
  •  
  • <!--- Dump out customer pets. --->
  • <cfdump
  • var="#qCustomerPet#"
  • label="Customer-Pets"
  • />

Running the above code, we get the following CFDump output:


  
  
  

 
Pet Clinic Query Data Joined In Query of Queries  
 
  
  

XML is a really great data transportation language and XPath() support in ColdFusion makes it fairly easy to work with; but, it's still very labor intensive. ColdFusion 8 has added additional support for XPath() to make it even easier. I hope that this has helped in some way.

Download Code Snippet ZIP File

Comments (5)  |  Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page



Adobe ColdFusion 8.0.1 Update - Helping Programmers To Be Signifanctly Less Girlie - Download ColdFusion 8 Update 8.0.1 Now.

Reader Comments

Wow. I'd gotten so stuck on the idea of continuing the multi-value flow from the db, it hadn't occurred to me to normalize it post-xml. Your solution has helped me greatly, and spared me from pulling the few hairs I have left. Thank you so much.

Posted by Chris Dunbar on Dec 21, 2007 at 11:49 AM


@Chris,

Glad to help :) Working with XML is mostly grunt work - once you get it into nice queries, you can really access it easily.

For anyone else interested, there is some ColdFusion 8 only stuff, but all of it can be easily modified to work with ColdFusion 7.

Posted by Ben Nadel on Dec 21, 2007 at 11:51 AM


There is also a rather handy tags that convert XML to cf structures for easier handling.
CFX_XML
http://www.adobe.com/cfusion/exchange/index.cfm?event=extensionDetail&extid=1002711

CF_EASYXML
http://www.adobe.com/cfusion/exchange/index.cfm?event=extensionDetail&extid=1019203

Posted by Russ Michaels on Jan 10, 2008 at 3:21 PM


Hi Ben;
This is exactly what i need. Thanks. But I am getting a loop error

Attribute validation error for tag CFLOOP.
The tag does not allow the attribute(s) ARRAY. The valid attribute(s) are COLLECTION,CONDITION,DELIMITERS,ENDROW,FROM,INDEX,ITEM,LIST,QUERY,STARTROW,STEP,TO.

Please try the following:
Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debugging Settings, and select the Robust Exception Information option.
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.


Browser Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Tablet PC 1.7; .NET CLR 1.0.3705; .NET CLR 1.1.4322; FDM; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022)
Remote Address 127.0.0.1
Referrer
Date/Time 20-Apr-08 12:00 PM

Is there a way to resolve this?

Thanks
Lakshmi

Posted by Lakshmi Prayaga on Apr 20, 2008 at 1:05 PM


@Lakshmi,

The "Array" attribute is a ColdFusion 8 feature. If you are pre-ColdFusion 8, you have to convert the array loop into an index loop:

<cfloop
index="xmlMainNode"
array="#arrMainNodes#">

... becomes:

<cfloop
index="intNodeIndex"
from="1"
to="#ArrayLen( arrMainNodes )#"
step="1">

<cfset xmlMainNode = arrMainNodes[ intNodeIndex ] />

Hope that helps.

Posted by Ben Nadel on Apr 21, 2008 at 9:43 AM


Post Comment  |  Ask Ben


Home   |   Web Log   |   ColdFusion   |   Projects   |   Resume   |   Job Form   |   Search   |   Contact
Epicenter Consulting - Custom Software Solutions for Business Evolution HostMySite.com - The Leader In ColdFusion Hosting