Oracle DB xml records: how can I load in QlikView!

[#1]

Anyone had any experience on how to load this kind of data. It is an Oracla data type. Basically they store data in a field as an xml tagged text.

For example take Customer table. The columns are:

IDREC; XMLRECORD
1; <CustName>Cusomter1<\CustName><CustAddress>Addres1<\CustAddress> ... etc

So actually all fields for the Customer are included in the xml record.

Any idea?

Regards

John

[#2]

No , data is in XML format in Oracle database.

[deepakk]

Hi

Can you put you data in excel and send it across. I can check how we can crack it.

Deepak

[#4]

Guys any luck with this??

I am stuck here, Do you think this there is oracle driver for JDBC that will work??

[shree909]

Hi i am having the same isssue

did anyone got the solution

i have load the xml data which is in oracle database into the qlikview.

Is therea way that we can load this into the qv..

[#6]

Hi all,

May be this can help, using oracle xmltype getClobval() and ExtractValue function:

To view row records sample:

using this function getClobval()

Sample query :

SELECT a.Recid, a.xmlrecord.getClobval() xmldata FROM <table with xml record> a;

XML to Normal Field Table:

XML Record Samples:

<row>

     <CustName>Cusomter1<\CustName>

     <CustAddress>Addres1<\CustAddress>

<row>

Sample Query:

SET long 8000

WITH Customer AS

(

SELECT

1. a.recid,
2. a.xmlrecord AS dataXML

FROM <table with xml record> a

)

SELECT

     recid,

     ExtractValue(dataXML,'/row/CustName/text()') AS CustomerName,

     ExtractValue(dataXML,'/row/CustAddress/text()') AS CustomerAddress

FROM Customer;

hopefully can help,

Cheers,

MY

JUKE SOLUSI TEKNOLOGI

[#7]

Hello to all.

I am also the problem of extracting information from an Oracle table with column of type XMLType. If anyone has any suggestions please help.

-------------------------------------------------------- Português ---------------------------------------------------------

Olá a todos.

Também estou com este problema de extrair informações de uma tabela Oracle com campo do tipo XMLTYPE. Caso alguém tenha alguma sugestão favor contribuir.

[#8]

Friends, I think I resolved my issue as follows:

In the script created a QlikView QVD:

SQL SELECT extract(XML_DOCUMENTO,'/','xmlns="http://www.portalfiscal.inf.br/cte"').getClobVal() AS XML_DOCUMENTO

FROM XML_DOCUMENTO_FISCAL

This way the files were extracted and can work with the data to make the load of the QVD created.

I hope have helped.

P.S.: Please forgive my English.

---------------------------------------------------------- Português ----------------------------------------------------------

Senhores, acho que resolvi minha questão da seguinte forma:

No script do QlikView criei um QVD:

SQL SELECT extract(XML_DOCUMENTO,'/','xmlns="http://www.portalfiscal.inf.br/cte"').getClobVal() AS XML_DOCUMENTO

FROM XML_DOCUMENTO_FISCAL

Desta forma os arquivos foram extraídos e consigo trabalhar com os dados ao fazer o load do qvd criado.

Espero ter ajudado.