Skip to main content
W
weezerboy
Inspiring
February 4, 2014
Answered

How to handle NULL or blank XML fields

  • February 4, 2014
  • 2 replies
  • 5549 views

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>

This topic has been closed for replies.
Correct answer Carl Von Stetten

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.

2 replies

R
REEDPOWELL
Inspiring
February 4, 2014

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

W
weezerboyAuthor
Inspiring
February 4, 2014

I know the problem filed is the  FILE_NUMBER field

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

Carl Von Stetten
Carl Von Stetten
Braniac
February 4, 2014

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.

Carl Von Stetten
Carl Von StettenCorrect answer
Braniac
February 4, 2014

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices