XML Input stage mapping problem

[JPalatianos]

Hi,
The last job of our sequence simply takes an text file and writes to an XML file:

Sequential File ===>>Transformer ======>> XML Input stage

One of the fileds we are moving is called ActivityTime. I have a stage variable defined in the transformer as:
svActivityTime = if (IsNull(DSLink2.ActivityTime) or DSLink2.ActivityTime = '' or DSLink2.ActivityTime = ' ') then 'NULL' else DSLink2.ActivityTime


The activitytime outbound colum is mapped to the Stage Variable: svActivityTime
The text file does have values for around half the cases but the output XML file is showing:
<activitytime>
NULL
</activitytime> for all these tags.


I ran a couple of tests to see if I can figure out what is goingon.

First test: hard coded a value of '2009-01-30T18:00:00.000Z ' in the transformer derivation for the activitytime output column. This ened up producing
<activitytime>
'2009-01-30T18:00:00.000Z
</activitytime> for all these tags.


Second Test: I modified the job to include a Peek stage as follows:

Sequential File ===>>Transformer ======>> XML Input stage
||
||
||
||
V
Peek Stage

The peek Stage had the same mapping as the XML Input Stage.
The peek appropriately showed a mix of NULLs and Values.

Anything else I can look into?

Thanks -- John

[eostic]

it's hard to say exactly what your issue is, but at first glance it appears that perhaps there is something about the null/not null setting of the column, it's treatment in the transformer, and it's treatment in the xml stage, especially when everything comes out ok when you hard code it.

What is your setting in the xmloutput stage for the handling of blanks and nulls? That setting impacts the delivery of empty elements vs nothing at all for nulls. You might want to play with it a bit. Your level of aggregation may be confusing things also.....and would depend on "where" this activity element is within your (potentially) nested hierarchy.

Ernie

[JPalatianos]

The column is defined as VarChar 255 NULLABLE = NO on both the input and output side.

I have "Replace NULL's with empty values" and "Replace empty values with NULLs" checked


As far as the hierarchy it is at the same level as the
others (i.eActivitydate) and I have no issue with the others.
<activity>
<businessunit>
PruRetirement
</businessunit>
<activitydate>
2/21/2006
</activitydate>
<activitytime>
NULL
</activitytime>
<clientname>
<![CDATA[Verizon Investment Management Co]]>
</clientname>
<citystate>
Pittsburgh,PA
</citystate>
<details>
<note>
<![CDATA[Message]]>
</note>
</details>
<pruattendees>
<attendee>
Vamsi Nutalapati
</attendee>
</pruattendees>
<activitystatus>
Completed
</activitystatus>
<updatedate>
7/8/2009
</updatedate>
<companyid>
11286
</companyid>
<activityid>
00T8000000vGap0EAC
</activityid>
</activity>

[eostic]

...and what happens when you map it directly to the output link (no stage variable and no derivation logic) ?

For such a test you might want to reconsider your check boxes for null handling, and only check "replace NULLs with empty values"....

You should get a lengthy set of </activityTime> elements, some of which are blank originally and some which might be null.

Perhaps that isn't what you want (yet), but it will be an interesting debugging step, to see if you ever get any "actual" values.

Ernie

[eostic]

tag above should be <activitytime></activitytime> or <activitytime/> .

[JPalatianos]

Hi Ernie,
Thanks for all the tips/advice. I have tried your latest suggestion by mapping the input column DSLink2.ActivityTime to the output column activitytime directly in the transformer. I also have the "replace NULLs with empty values" as the only option checked.

Looks like now we are getting all the data we are looking for but our upstream vendor does not want to have any tags when there is an empty element. I see the only choices on the stage as "Open and Close tags" or "Single Tag" for the empty element style. Any suggestions as to how we can remove these tags? Would this need to be a post DS routine of some sort.
Thanks for all your help -- John

[JPalatianos]

Just wanted to add the following which I just received from our vendor:

There are two situations. The first is when there's an extraneous empty tag (which they don't want) and the second when there is no data and an empty tag is appropriate. Examples below:
Example 1:
<Parent >
<Child/>
<Child>
<Name>John Smith</Name>
<DOB>2/28/2000</DOB>
.
.
.
</Child>
</Parent>

In this case the empty "Child" element is extraneous and should be excluded from the output file because there is another "Child" Element with data

Example 2:

<Parent >
<Child/>
</Parent>

In this case, the empty "child" element is appropriate and should be included in the output file because there is no "child" data at all for the "Parent" element.

[eostic]

In my last posting, I had you check "replace NULLS with empty values" so that you would be sure to see the results of your test......

When you uncheck this, NULLS "do not appear" (meaning...no tag comes out at all...) ....that is the default behavior and sounds like what your vendor is looking for.

Erine

[JPalatianos]

I ran the extract with the the "replace NULLS with empty values" unchecked and the vendor is specifically pointing to the following situation with our latest extract:


Ex:
<pruattendees>
<attendee/> (If there is an attendee this tag should not be here)
<attendee>
John Peto
</attendee>
</pruattendees>

The <attendee/> tag should only be there if there are no attendees as follows:

<pruattendees>
<attendee/>
</pruattendees>

[eostic]

In this example, that is only likely to happen if you had a "blank" value coming in for one of your rows.... meaning that one row had an attendee value of "John" and another row had an attendee value of " ". It's unlikely that you would get two attendees here unless there were two rows coming in....

btw...what is your repeating element (key) for the input link? Is it at the same level of the final xml document as attendee?

Ernie

[JPalatianos]

Ernie,
Once again, i want to thank you for all your advice and suggestions!! With the following changes the Vendor is happy with our output:
I have unchecked both
"Replace NULLs with empty values" and Replace empty values with NULL's"
and removed the designation of "KEY" for the attendee column on the XML stage input tab.
Thanks - - John