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

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

Mon, 19 Apr 2010 18:20:53 GMT

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

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

mike_garcia — Mon, 19 Apr 2010 19:37:49 GMT

Hi,

Have you tried to load the xml as Table File (the same way you would load an Excel file, or a QVD)?

I have loaded XML tables that way successfully.

Mike.

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

Tue, 20 Apr 2010 07:56:32 GMT

hi,

I know that I can load xml files as tables. The problem is that I do not have xml files but a database (oracle) and the records in that field are stored as xml data.

Regards,

John

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

Thu, 27 May 2010 14:46:18 GMT

Hello John,

Did you have any luck with this? I'm also trying to load xml froma database record.

Scott

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

Fri, 28 May 2010 14:57:00 GMT

No unfortunatelly no. Seems like it can't be done by default. Just with work araounds: like loading the records as text and then pasing the text...

Regards,

John

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

Fri, 24 Sep 2010 15:50:48 GMT

I have the same problem, any luck with it?

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

Thu, 01 Mar 2012 11:43:47 GMT

Does any one have the solution for the problem ? if you have please share the same. Thanks

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

deepakk — Thu, 01 Mar 2012 12:22:24 GMT

HI,

I have used macro to solve this kind of issue. I know that this isn't the stright forward solution. But this works fine for my scenario.

I have attach the application .You can modify the macro as per your requirement.

Deepak

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

Thu, 01 Mar 2012 12:35:51 GMT

Hi Deepak

Thanks for your reply, does your solution read from Oracle XML database ? I think it seems different what I am looking. Actually I have to connect Oracle XML database dynamically .

Best Regards

Zubair Khan

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

deepakk — Thu, 01 Mar 2012 12:39:49 GMT

Hi,

No I am getting from SQL Server. I don't think the database matter. what matter is how the data is lying in rows or columns or some other weird formats.

Correct me if I am wrong

Deepak

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

Thu, 01 Mar 2012 12:55:40 GMT

No , data is in XML format in Oracle database.

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

deepakk — Thu, 01 Mar 2012 13:12:05 GMT

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

Deepak

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

Mon, 05 Mar 2012 19:44:40 GMT

Guys any luck with this??

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

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

shree909 — Tue, 08 Jan 2013 18:13:43 GMT

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..

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

Tue, 25 Jun 2013 06:32:34 GMT

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

a.recid,
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,

JUKE SOLUSI TEKNOLOGI

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

Mon, 23 Sep 2013 13:41:19 GMT

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.

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

Mon, 23 Sep 2013 19:08:27 GMT

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.