How to handle NULL or blank XML fields

[weezerboy]

I am parsing an xml file and turning it into a query object. Everything good until I get a NULL or misssing value in my XML file and then I start getting error messages like

The value '' cannot be converted to a number.

So how can I check for these missing or NULL values in the XML and then set them to a value  want so they dont keep appearing as ''?

Here is my code: It runs great until i hit a missing value

<cfset mydoc = XmlParse(myxml)>

<!--- get an array of employees --->

<cfset emp = mydoc.ROWSET.XmlChildren>

<cfset size = ArrayLen(emp)>

  <cfset orderquery = QueryNew("FIRST_NAME, LAST_NAME,FILE_NUMBER,JOB_TITLE_DESCRIPTION") >

<cfset temp = QueryAddRow(orderquery, #size#)>

<cfloop index="i" from = "1" to = "#size#">

  <cfset temp = QuerySetCell(orderquery, "FIRST_NAME",

        #mydoc.rowset.ROW.FIRST_NAME.XmlText#, #i#)>

  <cfset temp = QuerySetCell(orderquery, "LAST_NAME",

        #mydoc.rowset.ROW.LAST_NAME.XmlText#, #i#)>

  <cfset temp = QuerySetCell(orderquery, "FILE_NUMBER",

        #mydoc.rowset.ROW.FILE_NUMBER.XmlText#, #i#)>

  <cfset temp = QuerySetCell(orderquery, "JOB_TITLE_DESCRIPTION",

        #mydoc.rowset.ROW.JOB_TITLE_DESCRIPTION.XmlText#, #i#)>

</cfloop>

[Carl Von Stetten · Accepted solution]

Which column is throwing the error?  Since you don't specify data types in your QueryNew call, I am assuming it is FILE_NUMBER.  In which case if the first record you process has a number in that column, then CF will implicitly set that column to be numeric.  If this is your intent, fine.  Otherwise, specify data types explicitly.

If that column should be numeric, then you will have to peform some checking before writing to the query cell.  Since the QueryAddRow() statement creates a bunch of empty rows with null values in all of the columns, you just have to skip over writing to the cells that should be null:

<cfif Len(mydoc.rowset.ROW.FILE_NUMBER.XmlText)>

     <cfset QuerySetCell(orderquery, "FILE_NUMBER", mydoc.rowset.ROW.FILE_NUMBER.XmlText, i)>

</cfif>

Notice a few things I left out of the code:

• No # signs - you only need these within <cfoutput> blocks to output variables to the screen.  They are almost never needed within function calls.
• No "temp =" in the <cfset> tag.  They are unnecessary if the operation on the right side of the equal sign is not returning a value that you need to store in a variable, as in this case.

HTH,

-Carl V.

[REEDPOWELL]

This is a common problem when processing xml files.  You need to determine whether the xml element is actually present, but with a null value, or if it is completely absent.  If your document isn't very large then you could just put a CFDUMP after the xmlParse(), otherwise you would put it at the top of the CFLOOP, followed by a CFFLUSH, and just look at the last dump before the error.

If the offending value actually has an element in the doc but has an attribute of NULL, then you can test for that.  If the element doesn't exist (which is the case I run into most ofter), then you need to preface those lines of code with ifStructKeyExists() to see if the element is really there, and to set a default value for the querySetCell() to use.

hth, -reed

[weezerboy]

I know the problem filed is the  FILE_NUMBER field

How would I use ifStructKeyExists()  & querySetCell()  in that case?

[Carl Von Stetten]

I'm not that great with XML, but I think you can modify my previous example:

<cfif StructKeyExists(mydoc.rowset.ROW, "FILE_NUMBER")>

     <cfset QuerySetCell(orderquery, "FILE_NUMBER", mydoc.rowset.ROW.FILE_NUMBER.XmlText, i)>

</cfif>

Although without seeing the actual XML structure, I'm just guessing.

-Carl V.

[BKBK]

What about just adding an if-statement like this:

<cfif size GTE 1>

<cfset orderquery = QueryNew("FIRST_NAME, LAST_NAME,FILE_NUMBER,JOB_TITLE_DESCRIPTION") >

<cfset temp = QueryAddRow(orderquery, size)>

<cfloop index="i" from = "1" to = "#size#">

    <cfset temp = QuerySetCell(orderquery, "FIRST_NAME", mydoc.rowset.ROW.FIRST_NAME.XmlText, i)>

    <cfset temp = QuerySetCell(orderquery, "LAST_NAME", mydoc.rowset.ROW.LAST_NAME.XmlText, i)>

    <cfset temp = QuerySetCell(orderquery, "FILE_NUMBER", mydoc.rowset.ROW.FILE_NUMBER.XmlText, i)>

    <cfset temp = QuerySetCell(orderquery, "JOB_TITLE_DESCRIPTION", mydoc.rowset.ROW.JOB_TITLE_DESCRIPTION.XmlText, i)>

</cfloop>

</cfif>

[REEDPOWELL]

The suggested CFIF would be unnecessary - the CFLOOP is going to handle the case of the empty document for you. Carl had most of the solution in his code, just no handling of the ELSE case for a default:

<cfif StructKeyExists(mydoc.rowset.ROW, "FILE_NUMBER")>

     <cfset QuerySetCell(orderquery, "FILE_NUMBER", mydoc.rowset.ROW.FILE_NUMBER.XmlText, i)>

<cfelse>

     <cfset QuerySetCell(orderquery, "FILE_NUMBER", putyourdefaultvaluehere, i)>

</cfif>

Out of curiousity, just what are you doing the with query var once you get it created?  Just asking because your code looks a lot like the sample code in a lot of the CF books that demonstrate how to process an XML document, which have you creating the query var and then looping through the query to do thge real processing, rather that just loop over the document.  If it's a big XML doc, you end up with a bigger memory footprint because you have both the xml var and the query var, plus the fact that the query functions have always been very poor performers.  Just need to loop over the xml var.

fyi,

-reed

[Carl Von Stetten]

Reed,

Good point about setting a default.  Although, as I said in an earlier response, when you create a query using QueryNew() and add rows using QueryAddRow(), all of the columns in each row are set to NULL by default.  So unless you want something else to be put in those columns when the XML data doesn't have a value for it (like an empty string, or 'N/A', or something else), leaving them as NULLs is acceptable.  I usually trap for those NULLs on the output side and substitute some display value at that point in time.

-Carl V.