Reading XML Data

[Nagin]

Dear Ernie,

Thanks for your reply.

I was not entirely sure what you meant in your post being new to datastage.

I have spoken to IBM and they have shared me the following solution which I think may be exactly what you were explaining.

Step1: Create a job parameter of type pathname with the location of your xml file in the default value field.

Step2: Create a row genreator - In the output create a column called trigger with a type of char that is not nullable

Step3: Create a Transformer that that has the name of the paramemter defind in step 1 as the Derivation

Step4: create a xml_input stage

Stage Properties: - General tab
I have Validate input xml and enable grammer caching turned on
Stage Properties: - Transformation Settings tab
Include namesace declation is checked
Input Properties: - XML source tab
Column is the name of the column from the transformer task
URL/File Path is checked
Input Properties: - Columns tab
create column "path" not a key of type VarChar tha is not nullable
output Properties: - Transformation settings tab
Inherit stage properties is checked
output Properties: - columns tab


name - BRANCH_NO
derivation - test.BRANCH_NO
key - yes (other columns set to no)
SQL Type VarChar
Extended - Unicode
Length - 10
Nullable = no
Display = 10
Description /storeInformation/s/BRANCH_NO/text()

The rest should be self explanitory as per the tutorial

Cheers,

Paul

Hey Paul,
I used the above approach to read an XML file. But, I am getting the following error

"XML input document parsing failed. Reason: Xalan fatal error (publicId: , systemId: , line: 0, column: 0): An exception occurred! Type:XMLPlatformException, Message:Could not read data from file"

Any idea, why is it happening?

Thanks,
Nagin.

[Nagin]

Dear Ernie,

Thanks for your reply.

I was not entirely sure what you meant in your post being new to datastage.

I have spoken to IBM and they have shared me the following solution which I think may be exactly what you were explaining.

Step1: Create a job parameter of type pathname with the location of your xml file in the default value field.

Step2: Create a row genreator - In the output create a column called trigger with a type of char that is not nullable

Step3: Create a Transformer that that has the name of the paramemter defind in step 1 as the Derivation

Step4: create a xml_input stage

Stage Properties: - General tab
I have Validate input xml and enable grammer caching turned on
Stage Properties: - Transformation Settings tab
Include namesace declation is checked
Input Properties: - XML source tab
Column is the name of the column from the transformer task
URL/File Path is checked
Input Properties: - Columns tab
create column "path" not a key of type VarChar tha is not nullable
output Properties: - Transformation settings tab
Inherit stage properties is checked
output Properties: - columns tab


name - BRANCH_NO
derivation - test.BRANCH_NO
key - yes (other columns set to no)
SQL Type VarChar
Extended - Unicode
Length - 10
Nullable = no
Display = 10
Description /storeInformation/s/BRANCH_NO/text()

The rest should be self explanitory as per the tutorial

Cheers,

Paul

Hey Paul,
I used the above approach to read an XML file. But, I am getting the following error

"XML input document parsing failed. Reason: Xalan fatal error (publicId: , systemId: , line: 0, column: 0): An exception occurred! Type:XMLPlatformException, Message:Could not read data from file"

Any idea, why is it happening?

Thanks,
Nagin.

[eostic]

Could be a lot of things......Some initial suggestions would be .....Check your filenames, check your security rights, test with other xml documents, try using a Server Job instead to "get to know" xml Pack (use the Folder Stage in a Server Job [take a look at the xml entries in my blog below])......

Ernie

[Nagin]

Could be a lot of things......Some initial suggestions would be .....Check your filenames, check your security rights, test with other xml documents, try using a Server Job instead to "get to know" xml Pack (use the Folder Stage in a Server Job [take a look at the xml entries in my blog below])......

Ernie

I have been tempted to use a server job because of the folder stge. But, my understanding is we can not use server jobs or Basic transformers on a MPP server. Is that correct?

[chulett]

No.

[eostic]

I always evaluate the use of Server Jobs for XML, especially if (a) I'm not using QualityStage (b) I'm in a hurry and don't have time to mess with column import with datatype and nullabilityissues (c) I'll be dealing with a lot of large variable text strings. (d) I don't need high volume parallel processing.

Server Jobs are datatype agnostic and don't care much about long strings until you decide to drop them somewhere. It has simpler text functionality when I need to tear apart an xml string or chunk. It includes the very flexible folder stage....and it's "there" on every platform as Craig confirmed earlier.

The other issues above (a, b, c, and d) are also very important and come up very often, but I'd say that at least 30% of the time I'm still using Server Jobs.

Ernie

[pniland]

Hi Nagin,

I am new to datastage so I can not comment as to the best job type for your process. I can however gaurentee you that that above settings do work as I have just finished writing a few packages using the same method.

I do however, agree with the comments from eostic. This could be a permissions/networking problem. I am using a UNC path accross servers and this works a treat.

I did however have to start small with the xml until I understood it and built up the Package.

Try building an xml file as small as the following, importing 2 rows into 1 column. Once you get this add another test column and your crack it.

Additioanlly, try putting your xml file on a local drive to start with.

Good luck.

Paul


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<storeInformation xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<s>
<BRANCH_NO>3061</BRANCH_NO>
</s>
<s>
<BRANCH_NO>3062</BRANCH_NO>
</s>
</storeInformation>