Import XML file directly from internet to SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » Import XML file directly from internet to SQL...

Import XML file directly from internet to SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

jorge_gomes98

Posted Thursday, February 24, 2011 3:26 AM

Valued Member

Group: General Forum Members
Last Login: Monday, December 05, 2011 4:38 AM
Points: 66, Visits: 72

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

Post #1068767

« Prev Topic | Next Topic »

Permissions