Import XML file directly from internet to SQL Server

Question

Post reply

Import XML file directly from internet to SQL Server

jorge_gomes98

SSC-Addicted

Points: 486
More actions
February 24, 2011 at 3:26 am

#234262

Hi,
i need to read the file in the internet to my SQL Server directly, like this:
-----------------------------------------
DECLARE @CD TABLE (XMLData XML);
INSERT INTO @CD
SELECT *
FROM OPENROWSET(BULK N'http://www.w3schools.com/XML/cd_catalog.xml', SINGLE_BLOB) rs;
INSERT INTO dbo.CD_Info (Title, Artist, Country, Company, Price, YearReleased)
SELECT Title = x.data.value('TITLE[1]','varchar(100)'),
Artist = x.data.value('ARTIST[1]','varchar(100)'),
Country = x.data.value('COUNTRY[1]','varchar(25)'),
Company = x.data.value('COMPANY[1]','varchar(100)'),
Price = x.data.value('PRICE[1]','numeric(5,2)'),
YearReleased = x.data.value('YEAR[1]','smallint')
FROM @CD t
CROSS APPLY t.XMLData.nodes('/CATALOG/CD') x(data);
----------------------------------
it is possible?
The article http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx
is perfect for me but I can not use the c:\doc.xml, I have to use the http://xxxxxxx.xxxx.xml
I have an ASP page that reads the XML directly from the Internet, but I do not want my clients to read directly from the Internet, I want to read from my SQL server.
Can i use SSIS ? or DTS 2000?
---------------
the page is like this:
<%@ LANGUAGE="VBScript" CodePage ="1252" %>

<html>
<head>
<title>teste</title>
</head>
<body>
<h1>Noticias from XML</h1>
<script language="JavaScript">
function trim(str, chars) {
return ltrim(rtrim(str, chars), chars);
}
function ltrim(str, chars) {
chars = chars || "\\s";
return str.replace(new RegExp("^[" + chars + "]+", "g"), "");
}
function rtrim(str, chars) {
chars = chars || "\\s";
return str.replace(new RegExp("[" + chars + "]+$", "g"), "");
}
function showhide(element){
var e=document.getElementById(element);
if (e.style.display == "block")
e.style.display = "none";
else
e.style.display = "block";
}
var xmlDoc = new ActiveXObject("Microsoft.XMLDOM")
xmlDoc.async="false"
xmlDoc.load("http://www.jornaldenegocios.pt/funcionalidades/envio_terceros/index.php?.....user/password.....")
//xmlDoc.load("noticias2.xml")
nodes = xmlDoc.documentElement.childNodes
document.write(nodes.length + " noticias");
for (i=0;i<nodes.length;i++)
if (nodes.item(i).nodeType==1) {
nodes2 = nodes(i).childNodes
//if (trim(nodes2.item(1).text) == "Mercados" || trim(nodes2.item(1).text) == "Economia") {
document.write("<div class='noticia'><a href='#' onclick='showhide(\"noticia" + i + "\")'><strong> " + nodes2.item(1).text + "</strong> " + nodes2.item(2).text + "</a><br />")
document.write(" <div id='noticia" + i + "' class='artigo' style='display: none;'>" + nodes2.item(3).text + "</div>")
document.write("</div>")
//}
}
</script>
</body>
</html>
---------------------------------------

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Import XML file directly from internet to SQL Server

Cookies on SQLServerCentral