Transforming a flat file into XML

[bidsadw]

Hi,

I am trying to import a delimited (;) text file and convert it to an XML file.

For now it is a POC so I am not too concerned with levels and heirarchies.

I am trying to create a simple data file that can be read by Cognos products.

I can perform the ETL and create a sequential file. When I then try to create as an XML file, the Datastage processes show as green but the file is not created.

My research, so far, seems to indicate that I need to edit the
Document Setting - Header with
<?xml version="1.0" encoding="UTF-8" ?>
and
add XPath Expressions in the Description section in the Input tab of XML Output Stage.

I do not know the syntax for the XPath expressions.
Can someone help?
Do you have an example that takes a text file and converts it to XML?
Or, how would I code this sample text to convert it to an XML file?
123456; CA; 1000000

Sample output:
Client ID Country Code Revenue
123456 CA $1,000,000

My actual file will be quite large and would norrmally be solved via a proper datamart but I need to produce a temporary, quick turn-around solution.

Regards,

Frank

[chulett]

Sample output:
Client ID Country Code Revenue
123456 CA $1,000,000

I'm a little lost as your sample of the output you are trying to create is not XML at all, it's simply another flat file in a more 'report like' format. The forum software removes all of the "extra" spaces you have in there unless you wrap something in 'code' tags, btw. Like so:

Code: Select all

Client ID     Country Code     Revenue        
123456              CA        $1,000,000

Are you sure what you need is XML?

Go here and download the XML Best Practices zip file Kim is hosting for us, you'll find it invaluable for when you do need to work with XML.

[eostic]

I agree with Craig...it's not exactly clear what you are looking for, since the output you display, and the description of the objective (so that it can be read by Cognos) are not consistent with needing xml.

That being said, for the simple 3 columns you have, just put this in your Description properties (the string with the slashes for each of the 3 columns on the input link...)

Client ID /myXML/client/ClientID/text()
Country Code /myXML/client/CountryCode/text()
Revenue /myXML/client/Revenue/text()

There are far more ways you could configure an xml document from these three columns, but this would be a suitable default for most situations. The header will default by itself, unless you've told it not to. Use "aggregate" and send the data to a file (where XMLOutput is the final stage in the stream of links), or have an output link with a single column called "myXMLcontent" with a long varchar as the data type and a single '/' in the Description.

Ernie

[eostic]

..that isn't that easy to read.... start each string in the description property with the /myXML part....

Ernie

[chulett]

You'll end up with something like this:

Code: Select all

<client>
 <ClientID>123456</ClientID>
 <CountryCode>CA</CountryCode>
 <Revenue>$1,000,000</Revenue>
</client>

All in one line with proper headers (etc), of course, but is that more like what you actually need?

[bidsadw]

Hi again,

My example turned out to be more of a distraction than illustration. Sorry about the confusion.

I have a delimited (;) text file and need to convert it to an XML file.
I am told that Cognos 8 will process queries faster against an XML than it would against a text file.

For now, my goal is to simply read the text file, perform a basic trim of the unnecessary spaces and then output the file as XML.
The reading and trimming stages are working just fine. It looks like the XML output stage is working fine. - all green when job is done/ no errors.

But, the XML file is not created. However, using the same process, I can create a sequential file.

I suspect that I am missing some basic knowledge on creating the XML output.

Your help is appreciated.
Regards,

Frank

[eostic]

Are your input link columns and properties on XMLOutput now identical to my post up above?

Is your XMLOutput the last stage in the stream?

Are you certain that you have access rights to the location where you are writing the file?

Are you getting data down the link that goes to the XMLOutput?

Ernie