Loading XML Data into SQL Server (SQL Spackle)

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 » Loading XML Data into SQL Server (SQL...

49 posts, Page 5 of 5

«««1 2 3 4 5

Loading XML Data into SQL Server (SQL Spackle)

Rate Topic

Display Mode

Topic Options

Author

Message

LutzM

Posted Tuesday, January 11, 2011 4:12 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

Amy.G (1/11/2011)

...
I'm going to consider this permission to let it go and do other work.

And thanks for that article. It has proved very helpful.

Amy

I'm not sure if a forum post would qualify as permission to do other work. Wink

But I'm glad I've been of some kind of help though. Smile

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #1046197

Anil Maharjan

Posted Tuesday, January 11, 2011 10:58 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 6:18 AM
Points: 8, Visits: 91

Hello all,
Well finally what we concluded then cannot we import the complex XML data file or what ?.

Post #1046266

Kelsey Thornton

Posted Wednesday, January 12, 2011 12:24 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 04, 2013 4:10 AM
Points: 532, Visits: 281

Anil Maharjan (1/11/2011)

Hello all,
Well finally what we concluded then cannot we import the complex XML data file or what ?.

I think that the only conclusions we can draw are:

1. If the input is well-formed XML then we can import it using the given method.

2. HTML is NOT, in general, well-formed XML

3. (Contentious.) Well-formed XML elements can only contain HTML data if they are within a CDATA block.

Kelsey Thornton
MBCS CITP

Post #1046295

brito.santos

Posted Monday, February 07, 2011 2:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 21, 2012 2:31 PM
Points: 2, Visits: 17

Supose you had to load 1 or 2 Gb of Xml data from one file into 2 tables. What you'd consider the best choice, OpenRowSet or BCP?

Post #1059916

LutzM

Posted Monday, February 07, 2011 2:53 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

brito.santos (2/7/2011)

Supose you had to load 1 or 2 Gb of Xml data from one file into 2 tables. What you'd consider the best choice, OpenRowSet or BCP?

It depends. But details should be discussed in a separate thread... Wink

Post #1059929

jorge_gomes98

Posted Wednesday, February 23, 2011 8:38 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 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.

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

LutzM

Posted Wednesday, February 23, 2011 12:04 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

jorge_gomes98,
please open a separate thread for the issue you're struggling with.
The section you posted in should be realated to the article. More related than "it's an xml issue"....

Post #1068458

MrBool.US

Posted Monday, October 17, 2011 5:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 17, 2011 5:48 AM
Points: 2, Visits: 1

To add a valuable resource, I suggest you watch the 10 videos (they are free for viewing online) on the link below:
http://mrbool.com/course/E-learning-XML-on-SQL-Server/260

Post #1191243

SQLRNNR

Posted Tuesday, October 18, 2011 4:10 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

MrBool.US (10/17/2011)

To add a valuable resource, I suggest you watch the 10 videos (they are free for viewing online) on the link below:
http://mrbool.com/course/E-learning-XML-on-SQL-Server/260

Interesting set of videos.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1191859

« Prev Topic | Next Topic »

49 posts, Page 5 of 5

«««1 2 3 4 5

Permissions