DSXchange

Hi,
I need to generate following XML format. Could anyone advise me on this?

<import-record type-id="1" xmlns="http://www.simple.com/publications/api">
<native>

<field name="application-date">
<date>
<day>element.getStartDate().substring(3,5) </day>
<month>element.getStartDate().substring(0,2) </month>
<year>element.getStartDate().substring(6) </year>
</date>
</field>

<field name="title">
<text>
escapeForXML(element.getTitle())
</text>
</field>

<field name="funder-name">
<text>
escapeForXML(element.getSponsor())
</text>
</field>

<field name="start-date">
<date>
<day>element.getStartDate().substring(3,5) </day>
<month>element.getStartDate().substring(0,2) </month>
<year>element.getStartDate().substring(6) </year>
</date>
</field>
</native>
</import-record>

I used XML output stage to generate the following format

<import-user-requests>
<users>
<user>
<title>ABC</title>
<initial>I</initial>
</user>
</users>
</import-user-requests>.

Please throw me some light how to generate XML.

Thank you,

Use a third party "XML prettifyer" to post-process the XML produced by DataStage.

Tell us what you have tried so far....all the stage types for xml are documented and noted in 100's of threads here.......

Ernie

Thanks Ernie,
I searched on DSXchange/Google and tried in the following way.

/import-record[@type-id=1]/native/field[@name="application-date"]/date/day
/import-record[@type-id=1]/native/field[@name="application-date"]/date/month
/import-record[@type-id=1]/native/field[@name="application-date"]/date/year
/import-record[@type-id=1]/native/field[@name="title"]/text
/import-record[@type-id=1]/native/field[@name="funder-name"]/text
/import-record[@type-id=1]/native/field[@name="start-date"]/date/day
/import-record[@type-id=1]/native/field[@name="start-date"]/date/month
/import-record[@type-id=1]/native/field[@name="start-date"]/date/year
/import-record[@type-id=1]/native/field[@name="end-date"]/date/day
/import-record[@type-id=1]/native/field[@name="end-date"]/date/month
/import-record[@type-id=1]/native/field[@name="end-date"]/date/year
/import-record[@type-id=1]/native/field[@name="amount"]/decimal
/import-record[@type-id=1]/native/field[@name="award-date"]/date/day
/import-record[@type-id=1]/native/field[@name="award-date"]/date/month
/import-record[@type-id=1]/native/field[@name="award-date"]/date/year

I am getting the below output.

<import-record type-id="1">
<native>
<field name="application-date">
<date>
<day>
01
01
30
05
</day>
<month>
05
05
09
05
</month>
<year>
2003
2003
2012
2003
</year>
</date>
<text>
Arkasoft Inc
</text>
<decimal>
400000
</decimal>
</field>
</native>
</import-record>

I am expecting field name for every column but I am getting only one field name and all the dates (day, month and year) are getting under one day, month and year tags instead of separate tags for every date. Could you please suggest me a way to achieve this?

Thank you,

I also tried by making some/all columns as keys even though I am getting same output.

Thank you,

There is a lot for you to learn here, in order to come up with a technique that will build that xml.

Here are a few tips to look into.

First, the xml stages will generate individual repeating nodes for every "row" that is coming into the Stage. So...in your xml "goal" document, the repeating unit is "<field>". This means that you need each "row" that comes into your xml Stage to be about "one" field.

For the moment, we'll assume that "field" = "column" in DataStage....that means that you will have to perform a pivot, generating one row for each "column" that you have coming from the original source.

Let's say you have a link with col1, col2, and col3, and you have 10 rows. You will need to generate 30 rows (in this example), with details of col1 on the first row, for the first row of your input, and then details of col2 on the second row, for the first row of your input, and then details of col3 on the third row, again for the first row of your original input.....and so on and so forth. Then you will have the raw material needed to build xml with <field> as the main repeating node.

Lots to play with. Get to know pivoting techniques first.......and the from there, look at what needs to be done with the xml.

Ernie

Thanks Ernie,
I am able to drag columns out from pivot stage. So in my case each row is getting split in to 15 rows (4 date columns so each date has day, month and year so 4*3=12 and then title, funder, amount. Total 12+3=15). Could you please advise me how to proceed further after pivot stage output?

Thank you,

just work on one little piece at a time. if you now have pivoted your columns into multiple rows, and assigned a value in a transformer with the name of each column, then you could just generate a simple xml with one column going into the xmloutput stage called "fieldname"...

with xpath of

/myxml/allfields/field/@name

If you have a column called "column1" and you pivot that into its own row, and set the column after the pivot called "fieldname" to the string "column1", you will get something like:

<field name="column1"/>

in your result.

Ernie

Ernie,
I have a small doubt, I need to pass column/field name along with column value, am I right?

Before Pivot stage can I use transformer to pass the column/field name by hard coding column/field name. If it is so then I got the output as you advised me in previous post. Please advise me.

Thank you,

Field names will have to be hard coded. I don't know of any way short of writing a class using the java integration stage, to get the name of the current column.

Ernie