Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

<Personnel>

          <Employee>

                    <Name>17fnle4mqwvoc</Name>

                    <ID>1</ID>

                    <Age>55</Age>

                    <Gender>Male</Gender>

          </Employee>

</Personnel>

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

Is this possible, and how?

10 Replies
Not applicable

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

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

Not applicable

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

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

amien
Valued Contributor

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

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?

amien
Valued Contributor

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

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

Not applicable

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

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.

amien
Valued Contributor

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

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

Not applicable

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

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.

amien
Valued Contributor

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

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

i will paste you some code to get you started

Not applicable

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

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.

Community Browser