DSXchange

Hi, Here is the problem.
I need to load the data into an Oracle Table from an XML file.
Everything is fine except there is a problem with the loaded data.
The job is loading the data with the nearest tags. To be more eloborative, let me explain the scenario.
Eg:

<address>
<name>xyz</name>
<city>abc</city>
</address>
<address>
<name>xyz1</name>
<city>abc</city>
</address>

When i load the data from the above, The data in oracle is loading like,

NAME CITY
<name>xyz</name> <city>abc</city>
<name>xyz1</name> <city>abc</city>

I am suspecting this is a problem with XPath data in the metadata def.
But i've not yet reached the solution.
Can anybody help? Thanks in advance.

What does your XPATH look like?

Are there two problems? From the post it seems u have two problems.
First

prasadnittala wrote:The job is loading the data with the nearest tags.

To me it means, for the second row (or other rows than first), it is always reading nearest tags data instead of correspoding data from row. So for XML you have mentioned, are you getting values xyz for both rows?
Please clarify if this what you meant by nearest tags?

Then second problem

prasadnittala wrote:When i load the data from the above, The data in oracle is loading like,

Code: Select all

NAME                     CITY
<name>xyz</name>        <city>abc</city>
<name>xyz1</name>       <city>abc</city>

Which looks like - data loaded in the table is with tags i.e. instead of storingxyz it is stroring <name>xyz</name> i.e. with tags?
Is this the case?

Can you post the XPaths you have used while reading the XML mentioned? Normally if the data is getting parsed properly, problem can be of XPaths.

One more thing, when you have to post some data like tables or job design use the code option which allows to use tabs n spaces so that tables will look like tables.

Apologies for the ambiguity. Here is the example I used.

SOURCE FILE CONTENTS:

<?xml version="1.0" encoding="ISO-8859-1" ?>
- <CATALOG>
- <CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>
- <CD>
<TITLE>Hide your heart</TITLE>
<ARTIST>Bonnie Tylor</ARTIST>
<COUNTRY>UK</COUNTRY>
<COMPANY>CBS Records</COMPANY>
<PRICE>9.90</PRICE>
<YEAR>1988</YEAR>
</CD>

XPath Defs (From Metadata):[/b]

/CATALOG/CD/TITLE/text()
/CATALOG/CD/ARTIST/text()
/CATALOG/CD/COUNTRY/text()
/CATALOG/CD/COMPANY/text()
/CATALOG/CD/PRICE/text()
/CATALOG/CD/YEAR/text()

ORACLE OUTPUT:
<table border=1>
<tr>
<th>TITLE</th>
<th>ARTIST</th>
<th>COUNTRY</th>
<th>COMPANY</th>
<th>PRICE</th>
<th>YEAR</th>
</tr>

<tr>
<td><TITLE>Empire Burlesque</TITLE></td>
<td><ARTIST>Bob Dylan</ARTIST></td>
<td><COUNTRY>USA</COUNTRY></td>
<td><COMPANY>Columbia</COMPANY></td>
<td><PRICE>10.90</PRICE></td>
<td><YEAR>1985</YEAR></td>
</tr>
<tr>
<td><TITLE>Hide your heart</TITLE></td>
<td><ARTIST>Bonnie Tylor</ARTIST></td>
<td><COUNTRY>UK</COUNTRY></td>
<td><COMPANY>CBS Records</COMPANY></td>
<td><PRICE>9.90</PRICE></td>
<td><YEAR>1988</YEAR></td>
</tr>
</table>