| Author |
Message
|
| harry123 |
 Posted: Tue Jan 22, 2013 10:36 am Post subject: XML to CSV |
 |
|
Newbie
Joined: 30 Oct 2012
Posts: 9
|
Hi -
I am new to WMB. I am processing a xml file which has 2 records and converting into CSV format. But after processing i am getting only one record in that output file(CSV).I am using compute node for this transformation.
I am only getting the last record of my input message to the output csv file.
I have included a trace node and the output I receive in Trace node is only the last record.
I have used 1 message set and below is the code that I have written for transformation.
SET OutputRoot.Properties.MessageSet = 'G6HKLL4002001';
SET OutputRoot.Properties.MessageType = 'Employees';
SET OutputRoot.Properties.MessageFormat = 'Text_CSV';
DECLARE i INT 1;
FOR INREF AS InputRoot.XMLNSC.Employees.Employee[] DO
SET OutputRoot.MRM[i].Name = InputRoot.XMLNSC.Employees.Employee[i].Name;
SET OutputRoot.MRM[i].ID = InputRoot.XMLNSC.Employees.Employee[i].ID;
SET OutputRoot.MRM[i].Company = InputRoot.XMLNSC.Employees.Employee[i].Company;
SET i = i+1;
END FOR;
Can you please help where did I go wrong.
Thanks,
Harry |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Jan 22, 2013 10:45 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
1. You should DECLARE an INT without an initializer. Then the very next statement should SET the INT to the desired initial value.
In your current code, when WMB runtime processes the first message, the INT will be correctly init to 1. The second message through, the value of the INT will be incorrect.
2. ESQL is a one-based array language. INREF should start + 1.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 22, 2013 10:49 am Post subject: Re: XML to CSV |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| harry123 wrote: |
| Can you please help where did I go wrong. |
In your code & configuration. What you've written will do exactly what you've described.
What you need to do is a) build a message set that correctly describes a multi-row CSV file b) configure the output node to build the file as a series of records c) actually produce individual CSV records in your code.
I recommend you ask someone on your site to mentor you as you're new.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Jan 22, 2013 11:23 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
I assume that you are using v6.1 or v7. Otherwise you would be using the DFDL domain for parsing/writing of CSV. It's a lot easier than using MRM.
If you are new to WMB, I would recommend that you get hold of a trial version and experiment with DFDL. |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Jan 22, 2013 11:29 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
lancelotinc said:
| Quote: |
| 1. You should DECLARE an INT without an initializer. Then the very next statement should SET the INT to the desired initial value. |
I disagree. I don't see any reason why an INT should not be declared with an initializer. Especially when the increment is being done at the end of the loop.
| Quote: |
| In your current code, when WMB runtime processes the first message, the INT will be correctly init to 1. The second message through, the value of the INT will be incorrect. |
I must be misunderstanding what you are saying...would you like to rephrase that? |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Jan 22, 2013 11:39 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
OK - three consecutive posts on the same thread, but different points being made...
Your ESQL can probably be a lot simpler. You don't even need a loop. Just copy InputRoot.XMLNSC.Employees to OutputRoot.MRM - it's a single line.
Do *not* copy it to OutputRoot.MRM.Employees - because the MRM tree omits the top level, for reasons that I will not bother to explain now.
If the input XML has extra fields that you want to discard then you may need a loop to copy them over. You may find it easier to work with your MRM model if you add an artificial extra complex element called 'Employees' that contains the entire content of the message. That way, you *can* copy to OutputRoot.MRM.Employees because the real root element will be the parent of Employees.
See what I mean about DFDL being easier to work with...? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Jan 22, 2013 12:28 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| kimbert wrote: |
lancelotinc said:
| Quote: |
| 1. You should DECLARE an INT without an initializer. Then the very next statement should SET the INT to the desired initial value. |
I disagree. I don't see any reason why an INT should not be declared with an initializer. Especially when the increment is being done at the end of the loop.
| Quote: |
| In your current code, when WMB runtime processes the first message, the INT will be correctly init to 1. The second message through, the value of the INT will be incorrect. |
I must be misunderstanding what you are saying...would you like to rephrase that? |
Maybe its a bug (7.0.0.3 when last observed), but if I
| Code: |
| DECLARE X_COUNT INTEGER 1; |
and some way down the page
| Code: |
| WHILE X_COUNT < ( J_COUNT + 1 ) DO |
then some more way down the page
| Code: |
SET X_COUNT = X_COUNT + 1;
END WHILE; |
The first time through with the first message, the X_COUNT is set correctly to 1 at the beginning. On the second invocation, the X_COUNT remains the value last set by SET X_COUNT = X_COUNT + 1; which is not correct. What appears to me is happening is if X_COUNT is already declared, the initializer is not called when it is combined with the DECLARE statement.
I agree with you that this is not the correct behavior. The expected behavior is that X_COUNT should be initialized for every invocation not just the first.
The workaround to this bug is to explicitly SET X_COUNT every time to the initial value after the DECLARE statement.
My flow is SOAPInput-based and each unique invocation is a separate Web Service transaction.
The current phrasing is correct for the incorrect behavior observed.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| harry123 |
| Posted: Tue Jan 22, 2013 2:01 pm Post subject: |
|
|
Newbie
Joined: 30 Oct 2012
Posts: 9
|
Thanks for your replies.
I have figured it out.
I am generating 2 MRMs instead of 2 records in MRM and as a result the latest MRM is only produced in my output.
Thanks,
Harry |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 22, 2013 2:20 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| harry123 wrote: |
| I have figured it out. |
Yay you!
| harry123 wrote: |
| I am generating 2 MRMs instead of 2 records in MRM and as a result the latest MRM is only produced in my output. |
Yes you are. Or were.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
