Best way to search data from Web Service against a SQL Stored Procedure
Hello,
I feel I may be in a little over my head with this one and could do with some advice.
Currrently I am running a database for searching through technical data for a system and returning this information via a perl script from a stored procedure. This information is then used by another company for their front end.
I have now been asked to link into a customer database to refine the information that is being returned and have become stuck.
Firstly the data being retuned is from a web service which I have very little experiance of so am unsure how to handle the data.
Secondly after the data has been compared I then need to create a list of items that were removed from the technical data due to customer history then create a reference so that the record can be reproduced at a later date.
Now if it was all in SQL I could handle it fine as I could return the data from the Web Service into a temporary table and perform the comparisons I need, generate the code and then send the information as I do now.
What I am struggling with is getting the data into SQL, searched online and found examples using CLR but there are several methods and I am unsure which one to use. One I tried didn't seem to work.
The option would be to create my own web service, return the data as I do now to that then request the data from the other web service and then compare the two data sets.
Ideally I would like to keep it all in SQL as I only need to worry about one thing instead of three but are there any things I should be aware of doing this? Also what is the best way to do this, currently the web service is returning the data as a long string which then needs to be inserted into a table, can this be done via SQL?
Thanks,
Lee
I feel I may be in a little over my head with this one and could do with some advice.
Currrently I am running a database for searching through technical data for a system and returning this information via a perl script from a stored procedure. This information is then used by another company for their front end.
I have now been asked to link into a customer database to refine the information that is being returned and have become stuck.
Firstly the data being retuned is from a web service which I have very little experiance of so am unsure how to handle the data.
Secondly after the data has been compared I then need to create a list of items that were removed from the technical data due to customer history then create a reference so that the record can be reproduced at a later date.
Now if it was all in SQL I could handle it fine as I could return the data from the Web Service into a temporary table and perform the comparisons I need, generate the code and then send the information as I do now.
What I am struggling with is getting the data into SQL, searched online and found examples using CLR but there are several methods and I am unsure which one to use. One I tried didn't seem to work.
The option would be to create my own web service, return the data as I do now to that then request the data from the other web service and then compare the two data sets.
Ideally I would like to keep it all in SQL as I only need to worry about one thing instead of three but are there any things I should be aware of doing this? Also what is the best way to do this, currently the web service is returning the data as a long string which then needs to be inserted into a table, can this be done via SQL?
Thanks,
Lee
Aaron Jabamani
Pass the data as xml to to your SQL.So that all retrival and comparision can be done in SQL and final result can be returned from SQL.
ASKER
Thank you for that, I figured that would be the easiest way to do it.
Now my next question is how is the best method of getting the data to SQL from a WebService. I know it is using CLR but I have not done that before. Looked for a few examples on how to do this but some list options I don't have or they don't seem to work.
Is there an easy guide on how to do this quite simply?
Now my next question is how is the best method of getting the data to SQL from a WebService. I know it is using CLR but I have not done that before. Looked for a few examples on how to do this but some list options I don't have or they don't seem to work.
Is there an easy guide on how to do this quite simply?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
The other way round, I am trying to work out how to read the data from the web service in an SQL stored proc.
ASKER
Passing the XML directly to SQL from a web service worked perfectly, then used sp_xml_preparedocument to select from OpenXML to read the data into a table.