generating flat file from xml file

lstsaur · Post by **lstsaur** » Mon Mar 03, 2008 5:50 pm

He did use the External Source stage when he first started it. People just don't relized that "ls" approach wouldn't work in 7.5x2 version that he is using.

That's why I suggested him to start with a server job.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 03, 2008 6:30 pm

Yes it would. 7.5x2 installs on top of MKS Toolkit, so you have the full gamut of UNIX commands available.

lstsaur · Post by **lstsaur** » Mon Mar 03, 2008 7:37 pm

Ray,
I followed every step from Ernie's blog using External Source with "ls" in 7.5.x2 platform, it just wouldn't work complaining either invalid domain name or C: is invalid path or directory. I know that MKS Toolkit is available on 7.5x2, but when "ls" issued from the External Source stage, it just aborts the job. You know how buggy is for 7.5x2.

However, exact job and syntax running on my other 7.5.1A Linux machine works just fine.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 03, 2008 9:05 pm

The currently mapped drive's root is considered to be "/" for UNIX purposes*.

Therefore, if the DataStage project is on D: drive, then /tmp in UNIX is D:\tmp in Windows.

You may need to fudge things a little, using relative pathnames rather than unqualified pathnames, but "UNIX" commands like ls do work happily (given that the above snippet of knowledge is vital) from 7.5x2.

* The UNIX file system has a single root. Windows is a multiply-rooted file system.

lstsaur · Post by **lstsaur** » Tue Mar 04, 2008 12:42 am

Ray,
Tested one more time on 7.5x2 environment; I am still getting the following error:
External_Source_0: When processing argument -sourcelistWhen processing file: ls C:\tmp\*.xml | sort is not a valid path name: Invalid hostname: ls C

I am pretty sure it's a bug for 7.5x2 version.

kirankota79 · Post by **kirankota79** » Tue Mar 04, 2008 10:45 am

Hi lstsaur....i am following your idea of creating a server job. the job is running without any errors. It is showing that some rows processed between i/p seq file and xml i/p stage but shows zero rows between xml i/p stage and o/p seq file. I am getting the following warning:

XmlTest_server..XML_Input_1: XML input document value is empty or NULL. Column Name = "xmldoc"

What could be the reason?

kirankota79 · Post by **kirankota79** » Tue Mar 04, 2008 10:45 am

Hi lstsaur....i am following your idea of creating a server job. the job is running without any errors. It is showing that some rows processed between i/p seq file and xml i/p stage but shows zero rows between xml i/p stage and o/p seq file. I am getting the following warning:

XmlTest_server..XML_Input_1: XML input document value is empty or NULL. Column Name = "xmldoc"

What could be the reason?

lstsaur · Post by **lstsaur** » Tue Mar 04, 2008 11:45 am

Check your o/p seq file's XPath in the Description column. Do you have the XPath for id, order_no, city, state, zip in there?

kirankota79 · Post by **kirankota79** » Tue Mar 04, 2008 12:25 pm

The following are descriptions for the columns in the o/p seq file:

/shiporder
/shiporder/@orderid
/shiporder/orderperson/text()
/shiporder/shipto/name/text()
/shiporder/shipto/address/text()
/shiporder/shipto/city/text()
/shiporder/shipto/country/text()
/shiporder/item/title/text()
/shiporder/item/note/text()
/shiporder/item/quantity/text()
/shiporder/item/price/text()

ray.wurlod · Post by **ray.wurlod** » Tue Mar 04, 2008 5:20 pm

UNIX commands don't work with Windows pathnames. Use the UNIX form of the pathname: for example ls -1 /tmp/*.xml (This assumes that DataStage server is installed on the C: drive.)

lstsaur · Post by **lstsaur** » Tue Mar 04, 2008 10:20 pm

Kirankota79,
Take column /shiporder out and check title as key. Then, job should work.

lstsaur · Post by **lstsaur** » Wed Mar 05, 2008 12:16 am

Ray,
I tried again with your suggestion, but the job still aborted with the following error:
External_Source_0: Could not open source list file "ls -l /tmp/*.xml | sort"

I have full permissions on the machine.
Aliado, for your time.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 2:24 am

It's not a file, it's the Filter command.

chulett · Post by **chulett** » Wed Mar 05, 2008 8:34 am

You two need to get a room, take your side conversation elsewhere.

kirankota79 · Post by **kirankota79** » Wed Mar 05, 2008 8:36 am

Hi lstsaur,

Thanks for replying to my posts. I will give the example xsd and xml file that i used to create sample server job( seqfile----->xml i/p------->seqfile).
I created the table definition with xsd file and i used xml file as input in the job. Below are the xsd and xml file. Please let me know how i need to do the settings this job. If this job runs it will be very helpful for me to understand how xml stages work.

XSD

<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="shiporder">
<xs:complexType>
<xs:sequence>
<xs:element name="orderperson" type="xs:string"/>
<xs:element name="shipto">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="item" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="title" type="xs:string"/>
<xs:element name="note" type="xs:string" minOccurs="0"/>
<xs:element name="quantity" type="xs:positiveInteger"/>
<xs:element name="price" type="xs:decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="orderid" type="xs:string" use="required"/>
</xs:complexType>
</xs:element>

</xs:schema>

XML

<?xml version="1.0" encoding="ISO-8859-1"?>

<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>

Thanks