How to generate xml output of below format

ramakrishna459 · Post by **ramakrishna459** » Thu Mar 28, 2013 11:17 pm

Hi all

I have below requirement for generating xml output.

Below are the columns DEPT and its value 01
likewise class and its value 02

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<RECS>
<REC>
<PROPERTY NAME="DEPT">
<VALUE>01</VALUE>
</PROPERTY>
<PROPERTY NAME="Class">
<<VALUE>02</VALUE>
</PROPERTY>
<PROPERTY NAME="SECTION">
<VALUE>ABC</VALUE>
</PROPERTY>
<PROPERTY NAME="SECTIONCODE">
<VALUE>1010</VALUE>
</PROPERTY>
<PROPERTY NAME="COLORcode">
<<VALUE>3435</VALUE>
</PROPERTY>
<PROPERTY NAME="CountOFREG1">
<VALUE>20</VALUE>
</PROPERTY>
<PROPERTY NAME="CountofREG2">
<VALUE>21</VALUE>
</PROPERTY>
</REC>
</RECS>

Could you please share ur suggestions.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 29, 2013 2:01 am

Welcome aboard. XML is free-format. DataStage does not generate "pretty" XML - it generates functional XML. There are lots of tools out there for prettifying XML.

ramakrishna459 · Post by **ramakrishna459** » Fri Mar 29, 2013 2:35 am

Thanks Ray.But am new to xml.our requirement is to generate the above xml for each record.could you please through some light on the difference between pretty and functional xml.

chulett · Post by **chulett** » Fri Mar 29, 2013 7:17 am

Being "new to XML" could turn this into a long and painful conversation as it's not really something you could learn in a forum post or twelve. Is there anyone you work with who knows XML and could help? Most of what you'd need to do is not specific to DataStage, so don't let that scare someone off from helping you.

Also, clarify for us your "below format" need. Ray was referring to the layout of what you posted rather than the details of each element and the fact that XML doesn't need to be indented in any fashion and is typically delivered in "one long string". A tool like I.E. can "pretty print" it to make it easier for a person to read but you don't typically generate it like that.

Now... do you have an xsd for the XML file you need to create? If not, can you ask for one? It will go a long way towards getting whatever stage you end up using in the proper format.

chulett · Post by **chulett** » Fri Mar 29, 2013 8:07 am

I'd also like to know your exact version of DataStage.

ramakrishna459 · Post by **ramakrishna459** » Fri Mar 29, 2013 9:55 am

I was able to generate the above format but am ending up using two xml output stages ...is there anyway to do in single xml stage.

I dont have XSD.

Thanks craig for your valuble response.

eostic · Post by **eostic** » Fri Mar 29, 2013 1:29 pm

This is one of those xml documents that has a fairly poor design.....it would have been better if the element names were truly the metadata...in this case, the property "name" is the metadata. We'll assume for the moment that things like DEPT, Class and SECTION are actually columns on your upstream data on a link....

Each "value" element will be easily created when you have a single "row" for each property and value combination. Accomplish that by first pivoting your data....so that if you have 10 columns per row...turn that into 10 rows. ...and then one single XML Stage will be able to create this, with value as your repeating element (the key) and only two columns coming into the stage...... PropertyName and PropertyValue.

The xpath (description property on the input link of the xmlOutput Stage), will be something like...

/recs/rec/property/@NAME
/recs/rec/property/value/text()

Do a lot of searching and reading thru the threads here if you aren't yet familiar with the use of XPATH in the Stage, or of repeating elements, or of how to do a PIVOT.

Ernie

ramakrishna459 · Post by **ramakrishna459** » Fri Mar 29, 2013 11:17 pm

you are true....if the elements name could be the column names and if there is any repitation element it could be easily to acomplish.