| Author |
Message
|
| anandsitha |
 Posted: Wed Jul 27, 2011 7:37 am Post subject: XML format To CSV format in WMB File Processing. |
 |
|
Acolyte
Joined: 26 Jul 2011
Posts: 59
|
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 mapping node for this transformation. I am using 2 message set one for xsd for source and other for CSV for target.
Regards
Sithanandam.V |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Jul 27, 2011 7:58 am Post subject: Re: XML format To CSV format in WMB File Processing. |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| anandsitha wrote: |
| I am processing a xml file which has 2 records and converting into CSV format. |
When you say "xml file which has 2 records", what you mean is:
| Code: |
<Totals>
<Record>1</Record>
<Amount>10</Record>
<Record>2</Record>
<Amount>100</Amount>
</Totals>
|
and you want 2 records "1,10" & "2,100" rather than "1,10,2,100" yes?
What steps have you taken inside the mapping node to split the inbound message into multiple outbound messages? The default in WMB is that 1 inbound = 1 outbound.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jul 27, 2011 8:04 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
That's actually a good point - can you do that with a mapping node? 
Clearly in ESQL doing this is like falling off a log that's been upended while wearing an anvil round your neck.
But given the number of times I've used a mapping node (twice!) my certainty is struck by sudden doubt...
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Jul 27, 2011 1:48 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
More details please:
- what does the input XML file look like. You don't need to post anything confidential - just describe the structure clearly and fully.
- How have you configured the FileInput node? List all relevant settings.
- What diagnosis have you already done, and what was the result? ( so that we don't tell you to do loads of stuff that you have already tried). |
|
| Back to top |
|
|
| anandsitha |
| Posted: Wed Jul 27, 2011 10:00 pm Post subject: |
|
|
Acolyte
Joined: 26 Jul 2011
Posts: 59
|
Hi-
I have used the below XSD and XML for input node.
Sample XML:
<?xml version="1.0"?>
<x:books xmlns:x="urn:books">
<book id="bk001">
<author>Writer</author>
<title>The First Book</title>
<genre>Fiction</genre>
<price>44.95</price>
<pub_date>2000-10-01</pub_date>
<review>An amazing story of nothing.</review>
</book>
<book id="bk002">
<author>Poet</author>
<title>The Poet's First Poem</title>
<genre>Poem</genre>
<price>24.95</price>
<review>Least poetic poems.</review>
</book>
</x:books>
Sample xsd:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:books"
xmlns:bks="urn:books">
<xsd:element name="books" type="bks:BooksForm"/>
<xsd:complexType name="BooksForm">
<xsd:sequence>
<xsd:element name="book"
type="bks:BookForm"
minOccurs="0"
maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="BookForm">
<xsd:sequence>
<xsd:element name="author" type="xsd:string"/>
<xsd:element name="title" type="xsd:string"/>
<xsd:element name="genre" type="xsd:string"/>
<xsd:element name="price" type="xsd:float" />
<xsd:element name="pub_date" type="xsd:date" />
<xsd:element name="review" type="xsd:string"/>
</xsd:sequence>
<xsd:attribute name="id" type="xsd:string"/>
</xsd:complexType>
</xsd:schema>
If it is a XML to XML using mapping node. I am able to get two records as shown below.
<NS1:books xmlns:NS1="urn:books">
<book id="bk001">
<author>Writer</author>
<title>The First Book</title>
<genre>Fiction</genre>
<price>44.95</price>
<pub_date>2000-10-01</pub_date>
<review>An amazing story of nothing.</review>
</book><book id="bk002">
<author>Poet</author>
<title>The Poet's First Poem</title>
<genre>Poem</genre>
<price>24.95</price>
<pub_date>2000-10-01</pub_date>
<review>Least poetic poems.</review>
</book></NS1:books>
But if is a the case of XML to CSV format I am getting the below output.
Ouput1.csv
The First Book, Writer
while mappling the 2 input fields to two csv format data.
1. title ---> mapped to Firstname
2. author ---> mapped to Lastname
and map will be like this below:
input node ------>mappin node------> output node
I have used 2 message set for source as well as target
Regards
Sithanandam.V |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Jul 28, 2011 12:20 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| OK - what about my other two questions? |
|
| Back to top |
|
|
| anandsitha |
| Posted: Thu Jul 28, 2011 1:11 am Post subject: |
|
|
Acolyte
Joined: 26 Jul 2011
Posts: 59
|
Input Directory - C:\Sitha\SimpleConverstion\Input
File Name or Pattern - Inputs1.XML
Action on Succeesful processing - Delete
Message domain - XMLNSC : For XML messages (namespace aware, validation, low memory use)
Message set - BooksMsgSet (GOASO88002001)
Message type
Message format
Message coded character set ID* - Broker System Default
Message encoding* - Broker System Determined
Parser Options - Immediate
Retry - Failure
Action on failing file - move to backout subdirectory
Records and Elements - Whole File
Validation - content and value
failure action - Exception
transaction mode - yes
Additional instance pool - use Pool associated with message flow.
Initially I checked that there may be some looping error. So i removed
msgmap:occurrence($source/bks:books/book) = 1.
Then i changed it into msgmap:occurrence($source/bks:books/book) = 2
it is not working. But it is working perfectly for both the source and target are same message set (ie. XML).
Any Idea?
Regards
Sithanandam.V |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Jul 28, 2011 1:17 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Put a Trace node before the output node. Take a trace of ${Root} for the XML->XML case, and then for the XML->CSV case. Compare the two traces. |
|
| Back to top |
|
|
| anandsitha |
| Posted: Thu Jul 28, 2011 1:58 am Post subject: |
|
|
Acolyte
Joined: 26 Jul 2011
Posts: 59
|
I found that my target CSV message set is the problem.
Now my output file is like this below
Writer,The First Book,Poet,The Poet's First Poem
but it should come like this
Writer,The First Book
Poet,The Poet's First Poem
Any Idea about how to give end of line in the csv message set
Regards
Sithanandam.V
[/img] |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Jul 28, 2011 2:56 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Find the element that represents a record. Find its complex type.
Change 'Data Element Separation' to 'All Elements Delimited' and set 'Delimiter' to '<CR><LF>'. |
|
| Back to top |
|
|
| anandsitha |
| Posted: Thu Jul 28, 2011 2:57 am Post subject: |
|
|
Acolyte
Joined: 26 Jul 2011
Posts: 59
|
Could you please assist me how to get trace. I am able to set breakpoints. but how to check the trace that i dont know.
Regards
Sithanandam.V |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Jul 28, 2011 3:53 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
If you mean user trace, then you use the command-line tools
mqsichangetrace
mqsireadlog
mqsiformatlog
...in that order.
But...why do you need to take a trace. Have you tried my suggestion? What happened? |
|
| Back to top |
|
|
| anandsitha |
| Posted: Thu Jul 28, 2011 5:02 am Post subject: |
|
|
Acolyte
Joined: 26 Jul 2011
Posts: 59
|
Your Suggestion give this below output.....Still small changes is there.
Writer,The First Book
,Poet,The Poet's First Poem
Configuaration
Group Terminator = <CR><LF>
Delimiter = ,
There is one ,(comma) starts with the second record.
Regards
Sithanandam.V |
|
| Back to top |
|
|
| anandsitha |
| Posted: Thu Jul 28, 2011 5:10 am Post subject: |
|
|
Acolyte
Joined: 26 Jul 2011
Posts: 59
|
Writer,The First Book,
Poet,The Poet's First Poem
there is one comma in the end of the first record.
Configuaration
Group Indication = <CR><LF>
Delimiter = ,
Regards
Sithanandam.V |
|
| Back to top |
|
|
| anandsitha |
| Posted: Thu Jul 28, 2011 5:14 am Post subject: |
|
|
Acolyte
Joined: 26 Jul 2011
Posts: 59
|
Writer
The First Book
Poet
The Poet's First Poem
Above output i got it if the configuaration is
Group Indicatior = <CR><LF>
Group Terminator = <CR><LF>
Delimeter = <CR><LF> |
|
| Back to top |
|
|
|
|
