Reading From an XML File

chowdary · Post by **chowdary** » Thu Mar 02, 2006 8:01 pm

People,

I need to read data from XML file and load into a Oracle DataBase, We are using DS 7.5, cna any one tell how i can do this, i tried using

XMLINPUT---->Transformer------>OCIStage

what are things i need to take care of when using XML Input Stage

When i am compiling i am getting a error as

Plug-in stage does not have required stream inputs(XML_INPUT.XML_Input_6)

Please thorow some suggestions

chulett · Post by **chulett** » Thu Mar 02, 2006 9:03 pm

Read up on the Folder stage and add it to the front of your job. It needs to bring the XML file(s) in and stream them into the XML Input stage so that it can parse them. Other than that, your basic job design is fine.

Make sure you import the metadata from your source XML file just like you would any other data source and then load it into the XML Input stage. The added bonus here is that it will generate the XPath expressions the stage needs in order to properly parse what comes in. Unless you enjoy typing in things like that by hand, that is.

Code: Select all

Folder---->XMLINPUT---->Transformer------>OCIStage

alisoviejo · Post by **alisoviejo** » Fri Mar 17, 2006 9:44 am

Hi Craig,

I am trying to locate the folder stage in the pallete and can not find it. Is it a plug-in (which is hard to believe). I did go to customize the pallete and search in all stage types for parallel.

We are on 7.5.1A, UNIX.

Thanks,

chulett · Post by **chulett** » Fri Mar 17, 2006 9:54 am

It's under the File branch along with the CFF, Sequential and Hashed File stages.

ushasunkara · Post by **ushasunkara** » Fri Mar 17, 2006 10:15 am

hi alisoviejo
r u using a parallel edition? bcoz the folder stage is only in server edition, i dont think u 've it in parallel canvas.

and chowdary, if you want to just read an XML file, and load into Oracle dB, jus follow it more easily,

take folder stage --> xml input stage --> Oracledb

and specify path of file or wildcard in folder stage, and in xml input stage, load the meta data from ur xsd (the xpath expressions) and the output columns wud be for the Oracle dB, and in Oracle db stage - specify the table name, dsn, username, passwd...

let me know if you can follow this, else ill explain in more detail,
bcoz we've done once, in parallel using a seq file -> xml input --> odbc stage...and it worked this way, reading xml file, validating it, and loading into tables...

Usha...

alisoviejo · Post by **alisoviejo** » Fri Mar 17, 2006 10:23 am

Thanks for the detailed posting, we are trying to read it from seq file , but it does not show any data and aborts with

XML_Input_1,0: Error: XSLT error: xsl:variable has an illegal attribute, style tree node: xsl:variable (, line 5, column 43)
XML source column not found. Column Name = "EXTRACT"

Looks like there is a meta data mismatch or in the format tab we are not giving the proper delimiters.
Will look into it.

ushasunkara · Post by **ushasunkara** » Fri Mar 17, 2006 10:32 am

Aliso,
in the seq file, you need to give a delimiter, at the end of all ur xml records, bcoz it reads all the records(whether it is 1 record or 2 or 2000 records) like a single string...

and in the seq file stage, Output tab, format - ull find a record delimiter string - so specify ur delimiter there...

chowdary · Post by **chowdary** » Fri Mar 17, 2006 5:36 pm

Hi Usha,

Thanks for the reply, i have a question regarding the metadata, how i can get the xsd?
For us some third party vendor will deliver will these XML files which we need to put in our DWH. All the gave is a word document which has Coloumn defnitions in a Tabular format in that word document. So i am confused how i can get the metadata. So can you please explain me how i can get or should we get anything else from them?
please suggest me...
Thanks
Chowdary

ushasunkara wrote:hi alisoviejo
r u using a parallel edition? bcoz the folder stage is only in server edition, i dont think u 've it in parallel canvas.

and chowdary, if you want to just read an XML file, and load into Oracle dB, jus follow it more easily,

take folder stage --> xml input stage --> Oracledb

and specify path of file or wildcard in folder stage, and in xml input stage, load the meta data from ur xsd (the xpath expressions) and the output columns wud be for the Oracle dB, and in Oracle db stage - specify the table name, dsn, username, passwd...

let me know if you can follow this, else ill explain in more detail,
bcoz we've done once, in parallel using a seq file -> xml input --> odbc stage...and it worked this way, reading xml file, validating it, and loading into tables...

Usha...

ushasunkara · Post by **ushasunkara** » Mon Mar 20, 2006 9:48 am

Hi Chowdary,
sorry for the late reply, jus got to see ur msg,
the XSDs are given by the vendor only, and these XSDs can be sitting anywhere on our desktop, and while importing the metadata,
load - XML definitions, u can browse for file - on ur desktop, and ull get all those nodes - where u can choose what all u need...
but if they dont give u XSDs , i think from the doc tables - u can derive XSDs, and that u can do using XMLSpy or something, (this point im really not sure of)...

chowdary · Post by **chowdary** » Mon Mar 20, 2006 6:45 pm

Thanks Usha, i will ask them to the give the XSD's

Once again thanks for the reply

Thanks
Chowdary

ushasunkara wrote:Hi Chowdary,
sorry for the late reply, jus got to see ur msg,
the XSDs are given by the vendor only, and these XSDs can be sitting anywhere on our desktop, and while importing the metadata,
load - XML definitions, u can browse for file - on ur desktop, and ull get all those nodes - where u can choose what all u need...
but if they dont give u XSDs , i think from the doc tables - u can derive XSDs, and that u can do using XMLSpy or something, (this point im really not sure of)...

shilpa79 · Post by **shilpa79** » Thu Mar 23, 2006 6:29 pm

rasi · Post by **rasi** » Thu Mar 23, 2006 6:48 pm

Rekha

Welcome to DSXchange.

You could post a new topic and have threads associated to that. If your question is not related to the existing one then post a new topic. Please don't hijack threads.

Thanks