October 30, 2009 at 8:45 am
Hello,
I have to integrate two applications, I have to read, from my sql Server, a xml file in a web url
I trying it with next code, but I don´t get the xml data.
This is my xml file, located in my web server:
<?xml version='1.0' encoding='UTF-8'?>
<libreria>
<nombre>Mi Book Store</nombre>
<libros>
<libro>
<titulo>Los pilares de la Tierra</titulo>
<autor>Ken Follet</autor>
<anyo>1996</anyo>
</libro>
<libro>
<titulo>El Senyor de los Anillos</titulo>
<autor>JRR Tolkien</autor>
<anyo>1954</anyo>
</libro>
<libro>
<titulo>Un pez llamado Wanda</titulo>
<autor>Perico de los Palotes</autor>
<anyo>2001</anyo>
</libro>
</libros>
</libreria>
This is the T-SQL that i have used:
DECLARE @completeUrl NVARCHAR(4000)
DECLARE @rc INT
DECLARE @objServHTTP INT
DECLARE @objServHTTPRecieve XML
DECLARE @objNodeList nvarchar(4000)
DECLARE @objCurrNode XML
DECLARE @iIterator INT
DECLARE @sXml NVARCHAR(4000)
DECLARE @iLen INT
set @completeUrl = "http://mywebserver/libreria.xml'
EXEC @rc = sp_OACreate 'MSXML2.ServerXMLHTTP', @objServHTTP OUT
EXEC @rc = sp_OACreate 'Msxml2.IXMLDOMDocument', @objServHTTPRecieve OUT
EXEC @rc = sp_OACreate 'Msxml2.IXMLDOMNode', @objCurrNode OUT
EXEC @rc = sp_OACreate 'Msxml2.IXMLDOMNodeList', @objNodelist OUT
EXEC @rc = sp_OAMethod @objServHTTP, 'OPEN', NULL, 'GET', @completeUrl
EXEC @rc = sp_OAMethod @objServHTTP, 'send'
EXEC @rc = sp_OAMethod @objServHTTP, 'responseXml', @objServHTTPRecieve OUT
/*
EXEC @rc = sp_OAMethod @objServHTTPRecieve, 'getElementsByTagName("ROW")', @objNodelist OUT
EXEC @rc = sp_OAMethod @objNodelist, 'length', @iLen OUT
while @iIterator<=@iLen
begin
SELECT @iIterator
EXEC @rc = sp_OAMethod @objNodelist, 'nextNode', @objCurrNode OUT
EXEC @rc = sp_OAMethod @objCurrNode, 'xml', @sXmlCurr OUT
select @iIterator = @iIterator+1, @sXml = @sXml + ' ' + @sXmlCurr
end
*/
CREATE TABLE #tmp ( titulo VARCHAR(100), autor VARCHAR(50), anyo INT )
INSERT INTO #tmp
SELECT
x.y.value( 'titulo[1]', 'VARCHAR(100)' ) AS titulo,
x.y.value( 'autor[1]', 'VARCHAR(50)' ) AS autor,
x.y.value( 'anyo[1]', 'INT' ) AS anyo
FROM @objServHTTPRecieve.nodes('libreria/libros/libro') x(y)
SELECT * FROM #tmp
drop table #tmp
This is the result (really: no result):
titulo autor anyo
-------- --------- -------
Any error? Any Idea?
Thanks in advance,
Jom.
October 30, 2009 at 8:49 am
Is there a reason to not set up the XML source as a web service and just access it that way?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 30, 2009 at 9:09 am
Hello.
It´s a requirement of the customer. They will published a web, where you will put your code in a web page (by querystring) and you will get a web page with a xml file (html with content type = text/xml).
Is there another way to access to the xml data to load them in my sql server?
Thanks a lot.
Jom.
October 30, 2009 at 9:20 am
Hi Jose
Try to get rid of these old "sp_OA" procedures. Since SQL Server 2005 you definitely should use .NET for this kind of task.
If possible, do things like this with external tools, like SSIS or a custom tool. If you have to do this within SQL follow the links posted by Paul White in this post:
http://qa.sqlservercentral.com/Forums/Topic805754-386-1.aspx
Or have a look at this sample:
Query a Web-Service with SQLCLR
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
October 30, 2009 at 9:40 am
Maybe I will try to use another method, but at this moment I would like to get run these commands (It is a personal obsession, I´m going to crazy if I don´t get it :hehe:).
I have look your links and:
Trying with this public web:
http://ws.geonames.org/countryCode?lat=40.417&lng=-3.703&type=xml
You get next xml file:
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
- <geonames>
- <country>
<countryCode>ES</countryCode>
<countryName>Spain</countryName>
<distance>0.0</distance>
</country>
</geonames>
So, Trying this code:
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Url as Varchar(MAX);
select @Url = 'http://ws.geonames.org/countryCode?lat=' + CAST(40.417 as varchar) + '&lng='+ cast(-3.703 as varchar) +'&type=xml'
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object
--load into Xml
Declare @XmlResponse as xml;
select @XmlResponse = CAST(@ResponseText as xml)
select @XmlResponse.value('(/geonames/country/countryName)[1]','varchar(50)') as CountryName
I get nex result (no result):
CountryName
------------
Null
Could you check it?
Do I have to do a special configuration in my SQL Server to use sp_OA methods or commands?
Thanks in advance,
Jom.
November 9, 2009 at 11:46 am
Hi Jose,
You need to turn on OLE Automation Procedures on your server.
Please run following SQL command and you will see the result as 'Spain'
exec sp_configure 'Ole Automation Procedures',1
go
reconfigure
-Amey
November 9, 2009 at 2:36 pm
you really should get rid of the ole automation and jump into SQLCLR, not only it will give you more liberty it might already be common ground for you.
if you dont have to support an application using SQL Server 2000 you theres no reason not to.
also, i think if ole automation was disabled he would get an error when trying to run the SPs
--
Thiago Dantas
--
Thiago Dantas
@DantHimself
November 10, 2009 at 6:13 am
Ok, thank you very much. I´m on holidays, I will check it at my return.
Thanks a lot of.
Jom.
November 17, 2009 at 3:45 am
Hello I get it, using OLE Automation Procedures !!!! but in another server (my notebook). :-D:-D:-D
I think that I have a problem in my production server because the T-SQL code doesn´t run.
In addition, I can run this code perfectly:
EXEC sp_configure 'show advanced option', 1
EXEC sp_configure 'Allow updates', 1
But If I run this code
EXEC sp_configure 'Ole Automation Procedures',1
GO
RECONFIGURE
I get these errors (i´m sorry, it´s in spanish)
Mens 15123, Nivel 16, Estado 1, Procedimiento sp_configure, Línea 51
La opción de configuración 'Ole Automation Procedures' no existe o se trata de una opción avanzada.
Mens. 5808, Nivel 16, Estado 1, Línea 1
No se admiten las actualizaciones ad hoc a los catálogos del sistema.
Any Idea? do I have to re-install or repair my SQL Server?
Thanks in advance,
Jom.
November 17, 2009 at 4:32 am
Hi
First first. "Allow Updates" is obsolete and does not work any more (thanks god!). Do not use this option.
Your error says all. "show advanced option" is not set until you run "RECONFIGURE". After this you can enable your "OLE...".
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
November 17, 2009 at 4:50 am
Ok, thank you, but when I set the option there´s not problem.
EXEC sp_configure 'show advanced option', 1
Se ha cambiado la opción de configuración 'show advanced options' de 1 a 1. Ejecute la instrucción RECONFIGURE para instalar.
the error is when I run RECONFIGURE
RECONFIGURE
Mens. 5808, Nivel 16, Estado 1, Línea 1
No se admiten las actualizaciones ad hoc a los catálogos del sistema.
That means "Ad Hoc updates not allowed at system catalog"
Any idea?
November 17, 2009 at 4:59 am
Hi Jose
This error is caused by your previously executed "allow updates". Have a look to BOL:
Setting Server Configuration Options
Try to reset this configuration with:
sp_configure 'allow updates', 0;
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
December 13, 2009 at 1:06 am
Hi ,
It's been time I been through this post. Have you found solution for this problem. I'm also trying to connect to similar web service throught SQL server 2005. I have unable ole automation procedures option.but this query below is still giving me null as output
webservice:http://ws.geonames.org/countryCode?lat=40.417&lng=-3.703&type=xml
query:
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Url as Varchar(MAX);
select @Url = 'http://ws.geonames.org/countryCode?lat=' + CAST(40.417 as varchar) + '&lng='+ cast(-3.703 as varchar) +'&type=xml'
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object
--load into Xml
Declare @XmlResponse as xml;
select @XmlResponse = CAST(@ResponseText as xml)
select @XmlResponse.value('(/geonames/country/countryName)[1]','varchar(50)') as CountryName
output:
CountryName
NULL
Thanks in advance for any help,
Amey
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply