XPath problems when converting flat file to XML

[Daniel Zackrisson]

Hello Datastage users!

I work on a data migration project and try to convert data from a tabular file to an XML-format. When I run my job i extract a row from the table like the second line below:

TYPE,level_type1,LINE,level_type2
C,1,AC,2

And want to create an XML output like this:

<Product_class>
_<name>C</name>
_<level_type>1</level_type>
</Product_class>
<Product_class>
_<name>AC</name>
_<level_type>2</level_type>
</Product_class>

I did this by writing (actually I imported tabledefinitions with these paths from the XMLMDimporter) XPath expressions in XML output stage, the column tab like this:

Column name,.......................,Description
TYPE,....................................,/Product_class/name/text()
level_type1,..........................,/Product_class/level_type/text()
LINE,.....................................,/Product_class/name/text()
level_type2,..........................,/Product_class/level_type/text()

The output I get is:

<Product_class>
_<name>C AC </name>
_<level_type>1 2</level_type>
</Product_class>

Please help me out, I'm stuck.. :p

/Dan

[chulett]

Rather than 'design' your own XPath expressions, get your hands on an .xsd of the desired output format. Use the Manager to import the metadata from the xsd and it will generate the appropriate XPath expressions for you to use.

[Daniel Zackrisson]

I used the stand alone XMLMDImporter.exe to generate the Xpaths from my schema so they should be valid. I suppose the resulting paths would be the same as if I would have used the manager? For some reason the expressions just ad new imputs into the same element.

I need to close the product_class node after the first two entries above (C,1) and then create a sibling node for the next two entries in the row (AC,2).

Does anyone know how to do that

[thebird]

Hi Daniel,

You should be able to do this by pivoting your input (TYPE and level_type). By this you would get your input as -

TYPE,level_type
C,1
AC,2

As you need the output as -

<Product_class>
_<name>C</name>
_<level_type>1</level_type>
</Product_class>
<Product_class>
_<name>AC</name>
_<level_type>2</level_type>
</Product_class>

the Xpath as below for you target file would be the right one.

Column name,.......................Description
TYPE..................................../Product_class/name/text()
level_type..........................,/Product_class/level_type/text()

Now if you pass the input rows to the XML output stage, you would get the desired.


Hope this helps.

The Bird.

[Lotus26]

HI

As part of my project I also need to convert a flat file to XML file. I am just having the schema file for that which is again a flat file. How should I import the metadata using this file through xm metadata importer..I am stuck with that .

Could you please let me know how should I proceed.

Thank you.