XML Input Stage Replace Empty Values with Null

reddy · Post by **reddy** » Tue Mar 04, 2008 10:48 am

Hi,

I am trying to replace the Empty values in the xml files to null. I have checked Replace Empty values with Null tab both on Stage/Transformation settings and Output/Transformation settings page. But i see empty values in teh output. Can anyone please help me? Thank you.

Reddy

jmarutz · Post by **jmarutz** » Tue Mar 04, 2008 1:53 pm

Are you sure the values are empty? Some non-printable characters don't display correctly in datastage, but are seen as values. Try either getting the length on those strings to make sure they're empty or write it to a file and vi it.

reddy · Post by **reddy** » Tue Mar 04, 2008 2:00 pm

They are empty. I can see that they are empty when i view tthe particular XML file.

reddy

eostic · Post by **eostic** » Tue Mar 04, 2008 4:14 pm

Hi Reddy...

How are you checking the nulls? The only safe way to check is to use the isNull() function against the columns, in a Transformer after your XMLInput Stage. Do something simple, like have a NullFlag column that is set to "yes" if [isNull(linkFromXMLInput.ColumnName)=1] ....otherwise you can't be sure.

If "replace empty values with nulls" is checked, both types of empty elements (<myElement/> and <myElement></myElement> will be NULL and xsi:nil and truly missing elements will also be NULL (more rows end up NULL than with default).

Ernie

ray.wurlod · Post by **ray.wurlod** » Tue Mar 04, 2008 5:27 pm

How do you claim to be able to "see" non-printing characters such as, perhaps, Esc in the XML file?

reddy · Post by **reddy** » Wed Mar 05, 2008 1:25 pm

Hi,
I am trying to capture the empty spaces and default them to null. So may i have to use a transforemr after teh XML Input and do something like IF input.columnname ='' then setnull() else input.columname. I think i will try this.

when i checked Replace empty spaces with null, i did not get even a single value as NULL and i can see a lot of empty spaces. Did I miss something in this process.

I think the values are empty in teh XML file beacuse i see a lot of this type of data in my xml file.

Process="" Version="" Priority=""

So, i thought these must be empty values and rightly when i extract them to a sequential file i get just a blank value for this particular field.

Thanks,

Reddy

eostic · Post by **eostic** » Wed Mar 05, 2008 7:08 pm

if you have xml content that looks like:

<myXML>
<myElement>
<rows>
<col1>123</col2>
<col2/>
<col3>456</col3>
</rows>
</myElement>
</myXML

and xpath for col2 that looks like /myXML/myElement/rows/col2/text()

and you check "Replace empty elements with null", then

isNull(LinkFromXML.col2) will yield "true".

What you do with it after that is up to you.....and simply in the domain of DataStage and no longer an xml issue. If you have this situation and isNull does not equate to true, let us know.

Ernie