Omit null columns in XML output

[lpadrta]

I'm not too XML savvy, and I'm modeling this server job on existing jobs. And I apologize that my terminology may be imperfect, too.

We are mapping multiple columns to individual output rows with tags assigned to each column value like this:

<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="5" y="45" catType="MULTI" subcatType="00012"/>
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="" y="" catType="MULTI" subcatType="00012"/>

I'm trying to figure out how to omit the x and y columns/tags when x="" and y="". So it should look like this:

<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="5" y="45" catType="MULTI" subcatType="00012"/>
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" catType="MULTI" subcatType="00012"/>

x and y are defined as varchar nullable=N, although setting them to nullable=Y doesn't seem to make any difference.

I've mapped @NULL to the x and y columns in the transformer just before the XML stage to create the output file. However I keep getting x="" y="" in the output file, and I only want them when there is a value in the column.

Am I missing something? It's probably something really obvious.... and simple....

Would appreciate some assistance.

Thanks, Lynda

[eostic]

Well...they're attributes, so they are treated a bit differently (than if they were sub-elements). You will probably need to zap them out of the string afterwards by sending the produced xml to a downstram transformer and use ereplace(). If these are the only two, that's the easiest solution. Just put an output link on your xmlOutput stage, with one big column (longvarchar and length 99999) and a single "/" (without quotes) in the Description property.

Downstream use ereplace to change those offending attributes to blank.

Ernie

[lpadrta]

Thanks, Ernie. As an old time server girl, using Ereplace is pretty logical. Following through on your suggestion:

I added a link from the existing XML stage to a transformer stage, and a link to another XML stage like so:

<existing logic>-->XMLa-->Xfm-->XMLb

Then in the transformer stage, I mapped a single column as LongVarChar 99999 on the input link and on the output link, with two nested Ereplaces, one for x and one for y. I put a single / in the transformer's description areas for both input and output. Then I formatted the new XMLb stage for output like the pre-existing XMLa stage was originally with a new filename.

When I ran the job, it failed with this message:
ds_ipcput() - row too big for inter stage rowbuffer

Not sure if I need to add something to tell DataStage how to read rows coming from the pre-existing XMLa stage?

Lynda

[ray.wurlod]

Increase the size of the Inter-Process Row Buffer so that it can contain at least one of your XML documents.

[eostic]

There are a lot of us 'ol server types around. . You shouldn't need the second xml stage, unless you are playing some other kinds of tricks. Just go directly to a seq stage and put a 000 in both the delimeter and quote properties and it will work fine. ....and I've passed HUGE documents without an issue ....perhaps you have interprocess row buffering turned on for your whole project? Do you need it for this job? I think you can just shut it off for "this" job and that probably will also make the problem go away.

Ernie

[eostic]

...-btw --- I did some brief checking....it appears that attributes are not allowed to be declared nillable in an xsd, which somewhat explains the behavior.

Ernie

[chulett]

There are a lot of us 'ol server types around.

<raises hand>

[lpadrta]

Thanks, you all are very helpful!

I don't have control over the project or server settings, but there must be a way to do what is needed.

I'll go back and keep working, and I'll let you know if I find success.

My last option is to manually edit the output files somehow. I may have to do this for the initial prototype, but I don't want to be in the regular business of editing files.

Lynda

[lpadrta]

I was able to link the XML file directly to output the seq file.

I set the seq file to Format 000 delimiter, 000 quote, set the line terminator to none, and I provided a single output column defined as varchar with / in the description column. In the XML stage, I turned off Comments. The resulting file from the seq stage looks like the XML file I was creating.

Thanks for your help!

Now this is working, I need a way to remove the x="" and y="" in the seq output file.

Onward...

[lpadrta]

Looks like the file produced from the sequential file stage omits the x="" and y="".

Mission accomplished.

Thanks all!