Discussion:
IBM SDI 7.2 CSV Parser
Add Reply
d***@gmail.com
2019-10-04 14:33:59 UTC
Reply
Permalink
Hello,

I have SDI project about SOAP Web Service.
I am using SOAP Web Service Connector and File Connector.
I successfully connected web service and I received datas however I want to parse this data to CSV file.
For now, when I parse the data to CSV it writes straight out of shape.
For example,

xmlString
"<?xml version=""1.0"" encoding=""UTF-8""?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:ns1=""http://192.168.24.11/web_services/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:SOAP-ENC=""http://schemas.xmlsoap.org/soap/encoding/"" SOAP-ENV:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/""><SOAP-ENV:Body><ns1:userListResponse><return xsi:type=""xsd:string"">
&lt;userListesi&gt;
&lt;users&gt;
&lt;userId&gt;1728&lt;/userId&gt;
&lt;userName&gt;YILRAK&lt;/userName&gt;
&lt;name&gt;YILDIRAY RAKICIOĞLU&lt;/name&gt;
&lt;surname&gt;&lt;/surname&gt;
&lt;tel&gt;&lt;/tel&gt;
&lt;mobile&gt;&lt;/mobile&gt;
&lt;email&gt;***@bxxxx.com.tr&lt;/email&gt;
&lt;status&gt;&lt;/status&gt;
&lt;/users&gt;
&lt;/userListesi&gt;
.........

I want to parse this data properly into the csv file.
Can you help me?

Thanks.
Eddie Hartman
2019-10-05 19:40:49 UTC
Reply
Permalink
Post by d***@gmail.com
Hello,
I have SDI project about SOAP Web Service.
I am using SOAP Web Service Connector and File Connector.
I successfully connected web service and I received datas however I want to parse this data to CSV file.
For now, when I parse the data to CSV it writes straight out of shape.
For example,
xmlString
"<?xml version=""1.0"" encoding=""UTF-8""?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:ns1=""http://192.168.24.11/web_services/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:SOAP-ENC=""http://schemas.xmlsoap.org/soap/encoding/"" SOAP-ENV:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/""><SOAP-ENV:Body><ns1:userListResponse><return xsi:type=""xsd:string"">
.........
I want to parse this data properly into the csv file.
Can you help me?
Thanks.
I don't have much experience using the SOAP Web Service Connector. Instead, I use the HTTP Client Connector for all HTTP work, SOAP, REST or otherwise. The xml you posted looks strange, since xml node tag start- and end-chars (<>) look to be encoded as &lt and &gt. If you have the full XML then you can quickly convert it to an hierarchical Entry:

hEntry = com.ibm.di.entry.Entry.fromXML(xmlString);

Note you can use any handy entry as well since this is a static method: work.fromXML()

Once you have the hEntry you can either convert to JSON and a JS object (which is what I usually do) or you can use the w3c DOMInterface methods available for an Entry object. This gives you XPath searching, among other tools.

If you have a valid XML payload and tell me what you'd like to parse this to, please post it and I'll whip up a show-and-tell AL for you :)

/Eddie
d***@gmail.com
2019-10-07 07:58:46 UTC
Reply
Permalink
Post by Eddie Hartman
Post by d***@gmail.com
Hello,
I have SDI project about SOAP Web Service.
I am using SOAP Web Service Connector and File Connector.
I successfully connected web service and I received datas however I want to parse this data to CSV file.
For now, when I parse the data to CSV it writes straight out of shape.
For example,
xmlString
"<?xml version=""1.0"" encoding=""UTF-8""?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:ns1=""http://192.168.24.11/web_services/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:SOAP-ENC=""http://schemas.xmlsoap.org/soap/encoding/"" SOAP-ENV:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/""><SOAP-ENV:Body><ns1:userListResponse><return xsi:type=""xsd:string"">
.........
I want to parse this data properly into the csv file.
Can you help me?
Thanks.
hEntry = com.ibm.di.entry.Entry.fromXML(xmlString);
Note you can use any handy entry as well since this is a static method: work.fromXML()
Once you have the hEntry you can either convert to JSON and a JS object (which is what I usually do) or you can use the w3c DOMInterface methods available for an Entry object. This gives you XPath searching, among other tools.
If you have a valid XML payload and tell me what you'd like to parse this to, please post it and I'll whip up a show-and-tell AL for you :)
/Eddie
Hi Eddie,

Thanks for your answer :)

I don't have much experience SDI. I don't know how to use what you say :)
I have a test.xml file.
It has very little data. We can succeed if we can pass this to csv appropriately. What should we do?
My first project on SDI. I'd appreciate it if you'd tell me what to do as an explanatory.
Thanks a lot.
d***@gmail.com
2019-10-07 07:59:40 UTC
Reply
Permalink
Post by Eddie Hartman
Post by d***@gmail.com
Hello,
I have SDI project about SOAP Web Service.
I am using SOAP Web Service Connector and File Connector.
I successfully connected web service and I received datas however I want to parse this data to CSV file.
For now, when I parse the data to CSV it writes straight out of shape.
For example,
xmlString
"<?xml version=""1.0"" encoding=""UTF-8""?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:ns1=""http://192.168.24.11/web_services/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:SOAP-ENC=""http://schemas.xmlsoap.org/soap/encoding/"" SOAP-ENV:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/""><SOAP-ENV:Body><ns1:userListResponse><return xsi:type=""xsd:string"">
.........
I want to parse this data properly into the csv file.
Can you help me?
Thanks.
hEntry = com.ibm.di.entry.Entry.fromXML(xmlString);
Note you can use any handy entry as well since this is a static method: work.fromXML()
Once you have the hEntry you can either convert to JSON and a JS object (which is what I usually do) or you can use the w3c DOMInterface methods available for an Entry object. This gives you XPath searching, among other tools.
If you have a valid XML payload and tell me what you'd like to parse this to, please post it and I'll whip up a show-and-tell AL for you :)
/Eddie
xmlString
"<?xml version=""1.0"" encoding=""UTF-8""?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:ns1=""http://192.168.24.11/web_services/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:SOAP-ENC=""http://schemas.xmlsoap.org/soap/encoding/"" SOAP-ENV:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/""><SOAP-ENV:Body><ns1:userListResponse><return xsi:type=""xsd:string"">
&lt;userListesi&gt;
&lt;users&gt;
&lt;userId&gt;1728&lt;/userId&gt;
&lt;userName&gt;YILRAK&lt;/userName&gt;
&lt;name&gt;YILDIRAY RAKICIOĞLU&lt;/name&gt;
&lt;surname&gt;&lt;/surname&gt;
&lt;tel&gt;&lt;/tel&gt;
&lt;mobile&gt;&lt;/mobile&gt;
&lt;email&gt;***@xxxxxx.com.tr&lt;/email&gt;
&lt;status&gt;&lt;/status&gt;
&lt;/users&gt;
&lt;/userListesi&gt;
&lt;userListesi&gt;
&lt;users&gt;
&lt;userId&gt;1730&lt;/userId&gt;
&lt;userName&gt;DENDER&lt;/userName&gt;
&lt;name&gt;DENİZ DERİNGÖR&lt;/name&gt;
&lt;surname&gt;&lt;/surname&gt;
&lt;tel&gt;&lt;/tel&gt;
&lt;mobile&gt;&lt;/mobile&gt;
&lt;email&gt;***@xxxxxxx.com.tr&lt;/email&gt;
&lt;status&gt;1&lt;/status&gt;
&lt;/users&gt;
&lt;/userListesi&gt;
&lt;userListesi&gt;
&lt;users&gt;
&lt;userId&gt;1738&lt;/userId&gt;
&lt;userName&gt;HAKGUN&lt;/userName&gt;
&lt;name&gt;HAKAN GÜNAYDIN&lt;/name&gt;
&lt;surname&gt;&lt;/surname&gt;
&lt;tel&gt;&lt;/tel&gt;
&lt;mobile&gt;&lt;/mobile&gt;
&lt;email&gt;***@xxxxxx.org&lt;/email&gt;
&lt;status&gt;1&lt;/status&gt;
&lt;/users&gt;
&lt;/userListesi&gt;
&lt;userListesi&gt;
&lt;users&gt;
&lt;userId&gt;1778&lt;/userId&gt;
&lt;userName&gt;FATOZC&lt;/userName&gt;
&lt;name&gt;FATMA ÖZCAN&lt;/name&gt;
&lt;surname&gt;&lt;/surname&gt;
&lt;tel&gt;031x xxx xx xx&lt;/tel&gt;
&lt;mobile&gt;&lt;/mobile&gt;
&lt;email&gt;***@xxxxxxx.com.tr&lt;/email&gt;
&lt;status&gt;&lt;/status&gt;
&lt;/users&gt;
&lt;/userListesi&gt;
&lt;userListesi&gt;
&lt;users&gt;
&lt;userId&gt;1779&lt;/userId&gt;
&lt;userName&gt;CUNYIL&lt;/userName&gt;
&lt;name&gt;A.CÜNEYT YILMAZ&lt;/name&gt;
&lt;surname&gt;&lt;/surname&gt;
&lt;tel&gt;031x xxx xx xx&lt;/tel&gt;
&lt;mobile&gt;&lt;/mobile&gt;
&lt;email&gt;***@xxxxxx.com.tr&lt;/email&gt;
&lt;status&gt;&lt;/status&gt;
&lt;/users&gt;
&lt;/userListesi&gt;
&lt;userListesi&gt;
&lt;users&gt;
&lt;userId&gt;1805&lt;/userId&gt;
&lt;userName&gt;NAITOP&lt;/userName&gt;
&lt;name&gt;NAİL TOPUZ&lt;/name&gt;
&lt;surname&gt;&lt;/surname&gt;
&lt;tel&gt;022x xxx xx xx&lt;/tel&gt;
&lt;mobile&gt;053x xxx xx xx&lt;/mobile&gt;
&lt;email&gt;***@xxxxxx.com.tr&lt;/email&gt;
&lt;status&gt;&lt;/status&gt;
&lt;/users&gt;
&lt;/userListesi&gt;
&lt;userListesi&gt;
&lt;users&gt;
&lt;userId&gt;1806&lt;/userId&gt;
&lt;userName&gt;SEZULU&lt;/userName&gt;
&lt;name&gt;SEZEN ULUSOY&lt;/name&gt;
&lt;surname&gt;&lt;/surname&gt;
&lt;tel&gt;021x xxx xx xx&lt;/tel&gt;
&lt;mobile&gt;&lt;/mobile&gt;
&lt;email&gt;***@xxxxxx.com.tr&lt;/email&gt;
&lt;status&gt;&lt;/status&gt;
&lt;/users&gt;
&lt;/userListesi&gt;</return></ns1:userListResponse></SOAP-ENV:Body></SOAP-ENV:Envelope>
"
j***@gmail.com
2019-10-07 10:42:00 UTC
Reply
Permalink
You need to "double decode" the XML to get what you want, as you
have XML encoded within XML.

Assume that you have the XML string in work.xmlString.

Then you can use a script like this, I use two different ways
to decode XML, just because I want to do that. Anyway,
you need to use the parser method to get multiple entries.

xmlString = work.getString("xmlString");
// Create a hierarchical Entry with the return value
hEntry = work.fromXML(xmlString, "Envelope/Body/userListResponse/return", "")

// Create a XMLParser to read the XML in the return attribute
xmlParser = system.getParser("ibmdi.XML2")
xmlParser.setInputStream(hEntry.getString("return"))
xmlParser.initParser()

// Read all the entries and write them to a CSV file
while ((a = xmlParser.readEntry()) != null)
CSVFileConnector.add(a)

The CSVFileConnector is a File Connector you have created, in AddOnly mode and Passive state, using a CSV Parser configured the way you want.

Note that there are a few typos in the sample data you provided, I guess you protected email addresses by removing them, but I had to replace "&gt." or "&g." with "&gt;.".

Here is some sample output I created with the above script:
surname;name;mobile;tel;userId;email;userName;status
;YILDIRAY RAKICIOĞLU;;;1728;***@xxxxxx.com.tr;YILRAK;
;DENİZ DERİNGÖR;;;1730;***@xxxxxxx.com.tr;DENDER;1
;HAKAN GÜNAYDIN;;;1738;***@xxxxxx.org;HAKGUN;1
;FATMA ÖZCAN;;031x xxx xx xx;1778;***@xxxxxxx.com.tr;FATOZC;
;A.CÜNEYT YILMAZ;;031x xxx xx xx;1779;***@xxxxxx.com.tr;CUNYIL;
;NAİL TOPUZ;053x xxx xx xx;022x xxx xx xx;1805;***@xxxxxx.com.tr;NAITOP;
;SEZEN ULUSOY;;021x xxx xx xx;1806;***@xxxxxx.com.tr;SEZULU;
d***@gmail.com
2019-10-07 13:38:36 UTC
Reply
Permalink
Post by j***@gmail.com
You need to "double decode" the XML to get what you want, as you
have XML encoded within XML.
Assume that you have the XML string in work.xmlString.
Then you can use a script like this, I use two different ways
to decode XML, just because I want to do that. Anyway,
you need to use the parser method to get multiple entries.
xmlString = work.getString("xmlString");
// Create a hierarchical Entry with the return value
hEntry = work.fromXML(xmlString, "Envelope/Body/userListResponse/return", "")
// Create a XMLParser to read the XML in the return attribute
xmlParser = system.getParser("ibmdi.XML2")
xmlParser.setInputStream(hEntry.getString("return"))
xmlParser.initParser()
// Read all the entries and write them to a CSV file
while ((a = xmlParser.readEntry()) != null)
CSVFileConnector.add(a)
The CSVFileConnector is a File Connector you have created, in AddOnly mode and Passive state, using a CSV Parser configured the way you want.
surname;name;mobile;tel;userId;email;userName;status
Hi Jens,

Thanks for your answer.
We did what you say however We can't data parse to csv file.
Can you send AssemblyLine file to me?

Thanks for your help.
I am waiting :)
Devran Uluçay
2019-10-08 07:51:31 UTC
Reply
Permalink
Post by j***@gmail.com
You need to "double decode" the XML to get what you want, as you
have XML encoded within XML.
Assume that you have the XML string in work.xmlString.
Then you can use a script like this, I use two different ways
to decode XML, just because I want to do that. Anyway,
you need to use the parser method to get multiple entries.
xmlString = work.getString("xmlString");
// Create a hierarchical Entry with the return value
hEntry = work.fromXML(xmlString, "Envelope/Body/userListResponse/return", "")
// Create a XMLParser to read the XML in the return attribute
xmlParser = system.getParser("ibmdi.XML2")
xmlParser.setInputStream(hEntry.getString("return"))
xmlParser.initParser()
// Read all the entries and write them to a CSV file
while ((a = xmlParser.readEntry()) != null)
CSVFileConnector.add(a)
The CSVFileConnector is a File Connector you have created, in AddOnly mode and Passive state, using a CSV Parser configured the way you want.
surname;name;mobile;tel;userId;email;userName;status
I wrote in the empty script, but nothing happens. Can you tell me how? Sample output the way I want.
Devran Uluçay
2019-10-08 08:42:25 UTC
Reply
Permalink
Post by j***@gmail.com
You need to "double decode" the XML to get what you want, as you
have XML encoded within XML.
Assume that you have the XML string in work.xmlString.
Then you can use a script like this, I use two different ways
to decode XML, just because I want to do that. Anyway,
you need to use the parser method to get multiple entries.
xmlString = work.getString("xmlString");
// Create a hierarchical Entry with the return value
hEntry = work.fromXML(xmlString, "Envelope/Body/userListResponse/return", "")
// Create a XMLParser to read the XML in the return attribute
xmlParser = system.getParser("ibmdi.XML2")
xmlParser.setInputStream(hEntry.getString("return"))
xmlParser.initParser()
// Read all the entries and write them to a CSV file
while ((a = xmlParser.readEntry()) != null)
CSVFileConnector.add(a)
The CSVFileConnector is a File Connector you have created, in AddOnly mode and Passive state, using a CSV Parser configured the way you want.
surname;name;mobile;tel;userId;email;userName;status
***@gmail.com
You can send AL file to above email adress.
j***@gmail.com
2019-10-10 06:14:06 UTC
Reply
Permalink
Unfortunately, I do not have your email address, so I cannot mail directly to you. But the code I mentioned should work, it worked for me, given the
conditions I posted.

If you give me some kind of indication of what happens, I could maybe try to help you with your issue, but just saying that "nothing happens" is not enough for me to make a guess.
Devran Uluçay
2019-10-10 06:41:31 UTC
Reply
Permalink
Post by j***@gmail.com
Unfortunately, I do not have your email address, so I cannot mail directly to you. But the code I mentioned should work, it worked for me, given the
conditions I posted.
If you give me some kind of indication of what happens, I could maybe try to help you with your issue, but just saying that "nothing happens" is not enough for me to make a guess.
Hi,

I shared e-mail adress. ***@gmail.com
Can you send AL File to me?
Devran Uluçay
2019-10-10 06:50:22 UTC
Reply
Permalink
Post by j***@gmail.com
Unfortunately, I do not have your email address, so I cannot mail directly to you. But the code I mentioned should work, it worked for me, given the
conditions I posted.
If you give me some kind of indication of what happens, I could maybe try to help you with your issue, but just saying that "nothing happens" is not enough for me to make a guess.
if my mail address does not appear,
My SDI Configuration;
-Feed
-Data Flow
Empty Script
CSVFileConnector(Passive Mode)


CSVFileConnector Output Map: Map All Attributes *
Connection: csv file path
Parser Field Seperator: ;


Empty Script Content:
task.logmsg("BasladiDevran");

var xmlString = "<?xml version='1.0' encoding='UTF-8'?>"
+"<SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/' xmlns:ns1='http://192.168.24.11/web_services/'"
+"xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"
+"xmlns:SOAP-ENC='http://schemas.xmlsoap.org/soap/encoding/' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>"
+"<SOAP-ENV:Body><ns1:userListResponse><return xsi:type='xsd:string'>"
+" <userListesi>"
+" <users>"
+" <userId>1728</userId>"
+" <userName>YILRAK</userName>"
+" <name>YILDIRAY RAKICIOĞLU</name>"
+" <surname></surname>"
+" <tel></tel>"
+" <mobile></mobile>"
+"
<email>***@birliksigorta.com.tr</email>"
+" <status></status>"
+" </users>"
+" </userListesi>";

xmlString = work.getString("xmlString");
// Create a hierarchical Entry with the return value
var hEntry = work.fromXML(xmlString, "Envelope/Body/userListResponse/return", "");

// Create a XMLParser to read the XML in the return attribute
var xmlParser = system.getParser("ibmdi.XML2");
xmlParser.setInputStream(hEntry.getString("return"));
xmlParser.initParser();

// Read all the entries and write them to a CSV file
while ((a = xmlParser.readEntry()) != null);
CSVFileConnector.add(a);
j***@gmail.com
2019-10-10 10:51:54 UTC
Reply
Permalink
I cannot see the SOAP WS Connector in your AssemblyLine.
I believe you may want to add it in the Dataflow,
before the Empty Script.
Devran Uluçay
2019-10-10 10:55:15 UTC
Reply
Permalink
Post by j***@gmail.com
I cannot see the SOAP WS Connector in your AssemblyLine.
I believe you may want to add it in the Dataflow,
before the Empty Script.
Is it not the case that I did this way for testing purposes now that there is a problem in the web service? is it also true?
j***@gmail.com
2019-10-10 11:08:02 UTC
Reply
Permalink
Oh, I see that you provide the xmlString as a constant. But then you
change it, probably to null, with the line

xmlString = work.getString("xmlString");

Remove that line, I think.

Loading...