February 24, 2011 at 3:26 am
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" %>
<!--#include file="CnnInc.asp"-->
<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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy