Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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

Not applicable
Author

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

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

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
Author

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

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
Author

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

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

i will paste you some code to get you started

Not applicable
Author

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.