Read XML from a URL in SQL Server

Question

Hello, I need to read xml from a URL using SQL Server 2005.

I´m using this code to read a xml from a file:

DECLARE @xmlDoc XML

SET

 

@xmlDoc = (

 

SELECT * FROM OPENROWSET

 

(BULK 'c:\file.xml', SINGLE_CLOB)AS xmlData)

SELECT

 

@xmlDoc

With this result:

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

I don´t find any way to read a xml from a url.

can anybody help me?

Thanks in advance,
Dukof.

Answer 1

Can you post a small example from your XML file and your expected results? If you really do mean "read an XML file from a URL", then I guess you would need some .net code, eg ASP. Perhaps can tell us a bit more about what you need.

Answer 2

Yes, of course. Thanks:

<?xml version='1.0' encoding='UTF-8'?>
<libreria>
 <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>

Answer 3

I´m  doing an integration between two applications. The  first one have a web portal where i can get a xml with a list of incidents. I have to load this incidents in my SQL Server. I think that i can do a ASP.Net program, but if I would be able to load this xml in SQL directly (using a trigger) will be better for me.

Regards,
Jom.

Answer 4

I´m trying this code:

set @completeUrl = 'http://myserver/myfile.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

But I don´t know how to access to xml values to insert each node tag in a field of a table.

Bye.

Answer 5

Assuming @sXML contains the XML you posted above at the end of the loop, then you could do something like this:

DECLARE @sXml XML

SET @sXml = '<libreria>
 <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>'

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 @sXml.nodes('libreria/libros/libro') x(y)

SELECT * FROM #tmp

You might consider using SSIS to access the file, or even OPENROWSET if SQL Server can 'see' the file, rather than those old spOA methods.

Answer 6

Hello,

Final test:

This xml file on 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 SQL:

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://129.181.201.102/Intranet_v2/A_Administracion/xml1/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 result (no result):

titulo    autor    anyo
-------- --------- -------

Any idea?

THANK YOU VERY MUCH for your help.

Regards,
Jom.

Answer 7

If you do

SELECT

 

 

@objServHTTPRecieve

just after you've populated it, is anything returned?

Answer 8

Null, nothing returned.

Jom.

Answer 9

So there is a problem with your sp_OA commands.  Try checking your return codes and output variables after each command.

Answer 10

Yes, thank you.

I´m checking the sp_OA commands.

Again, thank you for your attention.

Bye.

I hope to post the solution ;)