topic How-To Fetch/parse XML fields from a MS SQL database in QlikView

How-To Fetch/parse XML fields from a MS SQL database

Thu, 14 Jul 2011 11:42:12 GMT

Hi,

I'm trying something and I don't know if it is possible.

I have a MS SQL database with only 1 table containing XML fields, which look like this

<Name>17fnle4mqwvoc</Name>

</Employee>

</Personnel>

I want to load these XML's from the database and parse them like normal XML.

Is this possible, and how?

How-To Fetch/parse XML fields from a MS SQL database

Fri, 12 Aug 2011 12:06:34 GMT

I'm having similar problems, if you find any solution please share

How-To Fetch/parse XML fields from a MS SQL database

Fri, 12 Aug 2011 12:44:55 GMT

We ended up soliving by the SQL querry like this:

SELECT

ref.value ('Name[1]', 'varchar(50)') as Name,

ref.value ('ID[1]', 'varchar(50)') as ID,

ref.value ('Age[1]', 'varchar(50)') as Age,

ref.value ('Gender[1]', 'varchar(50)') as Gender

FROM [dbo].[ProfileXml] CROSS APPLY xml.nodes ('/Personnel/Employee') T(ref);

It's not the fastest solution but it works....

Re: How-To Fetch/parse XML fields from a MS SQL database

amien — Fri, 28 Sep 2012 15:11:08 GMT

slaaphoofd,

i'm looking for something similair. But i can't get it to work.

what is your fieldname where this xml string is in?

Re: How-To Fetch/parse XML fields from a MS SQL database

amien — Thu, 04 Oct 2012 08:55:01 GMT

i guess T in the above example is a Custom SQL-Function. Can't this be done more easy? i just need 1 node from the total XML. And i can't find any SQL Function to search and strip. ODBC can't consume that much SQL functions

Re: How-To Fetch/parse XML fields from a MS SQL database

Fri, 22 Feb 2013 14:19:32 GMT

Hi Amien,

Now i am trying to achieve something similar,here is my post actually

http://community.qlik.com/thread/73244?tstart=0

Can you please take a look at it,for me it looks like you had the same issue in this post.If so,can you please let me know what did you go to get around with this?

Thanks.

Re: How-To Fetch/parse XML fields from a MS SQL database

amien — Fri, 22 Feb 2013 14:26:00 GMT

I never fixed it. The problem was the ODBC commands where to limited to do this. Plus xml.nodes was a customized function. i created a SQL view for this. Like slaaphoofd did

Re: How-To Fetch/parse XML fields from a MS SQL database

Fri, 22 Feb 2013 14:27:16 GMT

So,did you achieve what you wanted with the sql view?Can you give me a brief explanation of what you did to workaround with the view and how did you end up loading the XML into QlikView?

Thanks.

Re: How-To Fetch/parse XML fields from a MS SQL database

amien — Fri, 22 Feb 2013 14:32:57 GMT

sent me a mail to amien90 -at- gmail.com

i will paste you some code to get you started

Re: How-To Fetch/parse XML fields from a MS SQL database

Fri, 22 Feb 2013 15:15:45 GMT

Hi slaaphoofd,

Can you please let me know if you got around with the solution.Actually i am also looking for same kind of thing,here is the link

http://community.qlik.com/thread/73244?tstart=0

I want a column from one of my SQL table to be loaded into Qlikview as XML?

Can you please help me with this.

Thanks.

Re: How-To Fetch/parse XML fields from a MS SQL database

Fri, 22 Feb 2013 17:52:26 GMT

Hi Garbiel,

Did you find the solution for this?I am also looking for something similar to this one.

If you do please help me .

Thanks