SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / Loading XML Data into SQL Server (SQL Spackle) / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 07:57:43 GMT20RE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]MrBool.US (10/17/2011)[/b][hr]To add a valuable resource, I suggest you watch the 10 videos (they are free for viewing online) on the link below:[url]http://mrbool.com/course/E-learning-XML-on-SQL-Server/260[/url][/quote]Interesting set of videos.Tue, 18 Oct 2011 04:10:50 GMTSQLRNNRRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxTo add a valuable resource, I suggest you watch the 10 videos (they are free for viewing online) on the link below:[url]http://mrbool.com/course/E-learning-XML-on-SQL-Server/260[/url]Mon, 17 Oct 2011 05:51:45 GMTMrBool.USRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxjorge_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"....Wed, 23 Feb 2011 12:04:38 GMTLutzMRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxHi, i need to read the file in the internet to my SQL Server directly, like this:-----------------------------------------DECLARE @CD TABLE (XMLData XML);INSERT INTO @CDSELECT *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.xmlI 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" %><!--#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.childNodesdocument.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>---------------------------------------Wed, 23 Feb 2011 08:38:04 GMTjorge_gomes98RE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]brito.santos (2/7/2011)[/b][hr]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?[/quote]It depends. But details should be discussed in a separate thread... ;-)Mon, 07 Feb 2011 14:53:34 GMTLutzMRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxSupose 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?Mon, 07 Feb 2011 14:34:34 GMTbrito.santosRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]Anil Maharjan (1/11/2011)[/b][hr]Hello all,Well finally what we concluded then cannot we import the complex XML data file or what ?.[/quote]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 XML3. (Contentious.) Well-formed XML elements can only contain HTML data if they are within a CDATA block.Wed, 12 Jan 2011 00:24:38 GMTKelsey ThorntonRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxHello all,Well finally what we concluded then cannot we import the complex XML data file or what ?.Tue, 11 Jan 2011 22:58:57 GMTAnil MaharjanRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]Amy.G (1/11/2011)[/b][hr]...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[/quote]I'm not sure if a forum post would qualify as permission to do other work. ;-):-DBut I'm glad I've been of some kind of help though. :-)Tue, 11 Jan 2011 16:12:12 GMTLutzMRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote]The following code is like using a hammer to drive in a screw. :pinch: So, it's for demonstration purposes only, NOT a recommended solution! The recommended solution would be to get clean XML data.[/quote]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.AmyTue, 11 Jan 2011 15:28:36 GMTAmy.GRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxOk, then we'd need to add the namespace declaration (result as above).Regarding your comment about the person sending you the file, here's what I'd say:Whoever sent you that file should remember that SQL Server is still a RDBMS and not a HTML garbage cleaner. But it can do that, too, if there's absolutely nothing else to do.But, if all you're asking SQL Server to do is to deal with this kind of stuff, you should consider getting a different tool... ;-)The following code is like using a hammer to drive in a screw. :pinch: So, it's for demonstration purposes only, NOT a recommended solution! The recommended solution would be to get clean XML data.[code="sql"]DECLARE @x XMLSET @x=' <p xmlns:tcm="http://www.tridion.com/ContentManager/5.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/1999/xhtml"><div id="content"><a name="ada-content" /><div id="browseBox"> <div class="roundedBox_top-left" /> <div class="roundedBox_top-right" /> <div class="licenseTitle"> <h1> <span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Title[1]">Aboveground Storage Tank (AST) Permits (>1 Million Gallons Total)</span> </h1> <div class="licenseDescription"> <span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Description[1]">Minnesota Pollution Control Agency (MPCA)</span> </div> </div> <div class="roundedBox_bottom-buff" /></div><div class="roundedBox_bottom-left" /><div class="roundedBox_bottom-right" /><div id="licenseDetail"> <div id="licenseRightColumn"> <h3 style="margin:0;line-height:100%;">Agency contact information:</h3></div></div></div></p>';WITH cte AS ( SELECT @x.query(' declare default element namespace "http://www.w3.org/1999/xhtml"; //span') col )SELECT c.value('.','varchar(200)') span_resultFROM cteCROSS APPLY col.nodes('declare default element namespace "http://www.w3.org/1999/xhtml"; span') T(c)[/code]Tue, 11 Jan 2011 15:22:52 GMTLutzMRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]LutzM (1/11/2011)[/b][hr]Here's a short example to query data like you've posted [/quote]I'm really impressed that out of that whole mess you were able to identify the only actual piece of data needed. I have been practicing and got the license title like you did, but then just as I felt I had it licked, a snag: [code="sql"]DECLARE @y XMLSET @y = '<h1> <p xmlns:tcm="http://www.tridion.com/ContentManager/5.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/1999/xhtml"> Facilities that have more than one million gallons capacity must obtain an individual permit from the MPCA according to Minnesota Rules Chapter <a href="http://www.revisor.leg.state.mn.us/arule/7001/">7001</a>.4200-4250. </br> </br> Owners of Aboveground Storage Tanks larger than 1,100 gallons must notify the Minnesota Pollution Control Agency (MPCA) of the existence of these tanks. In addition, tank owners are required to notify the MPCA of change of product or change of status of ASTs. </h1>'SELECT y.data.value('p[1]', 'varchar(500)') as Feesfrom @y.nodes('h1') y(data);[/code]I think the "xmlns"tcm..." is the problem, b/c when I delete that part, it works fine. But going through the whole file deleting would go against the whole point of this. Do you know a way to ignore it?Looking back, I guess I was hoping someone would've said "whoever sent you that file is an idiot, it's impossible to read!". Now I'm getting obsessed with it.AmyTue, 11 Jan 2011 14:37:20 GMTAmy.GRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxHere's a short example to query data like you've posted (please note that I added a few end tags to change it into a valid xml structure as per SQL requirements):[code="sql"]DECLARE @x XMLSET @x='<p><div id="content"><a name="ada-content" /><div id="browseBox"> <div class="roundedBox_top-left" /> <div class="roundedBox_top-right" /> <div class="licenseTitle"> <h1> <span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Title[1]">Aboveground Storage Tank (AST) Permits (>1 Million Gallons Total)</span> </h1> <div class="licenseDescription"> <span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Description[1]">Minnesota Pollution Control Agency (MPCA)</span> </div> </div> <div class="roundedBox_bottom-buff" /></div><div class="roundedBox_bottom-left" /><div class="roundedBox_bottom-right" /><div id="licenseDetail"> <div id="licenseRightColumn"> <h3 style="margin:0;line-height:100%;">Agency contact information:</h3></div></div></div></p>';WITH cte AS ( SELECT @x.query('//span') col )SELECT c.value('.','varchar(200)') span_resultFROM cteCROSS APPLY col.nodes('span') T(c)/* result setAboveground Storage Tank (AST) Permits (>1 Million Gallons Total)Minnesota Pollution Control Agency (MPCA)*/[/code]Tue, 11 Jan 2011 14:17:03 GMTLutzMRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]Amy.G (1/11/2011)[/b][hr]Steve,Do you mean I need to go into the file and type in [CDATA[... where all HTML appears? Amy[/quote]Well, run this little test:[code="sql"]declare @xml1 XML, @xml2 XML;set @xml1 = '<Content><![CDATA[<div id="contentcenter"><p>Perform a <b>hardware</b> check with the utility to assure the quality of the system driver, etc., etc.</p></div>]]></Content>';set @xml2 = '<Content><div id="contentcenter"><p>Perform a <b>hardware</b> check with the utility to assure the quality of the system driver, etc., etc.</p></div></Content>';select @xml1, @xml2;[/code]@xml1 has the <![CDATA[ ... ]]>, and @xml2 doesn't.Notice that in @xml2, that all of the HTML has become tokenized. From what I understand about HTML (very little), there are some tokens where the closing token is optional. If you have any of those in the XML file, SQL is probably going to throw an error. As is stands, since the HTML is tokenized, it might not get in the way of extracting the data. You'd have to test it to see. Basically, if it can be put into an XML datatype (either variable or table column), then you will probably be able to work with it without having to add the CDATA stuff.Tue, 11 Jan 2011 10:28:55 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]weharrelson (1/11/2011)[/b][hr]Sorry for the delay guys: DECLARE @x XMLSELECT @x= CONVERT(xml, BulkColumn, 2) FROM OPENROWSET(BULK 'C:\data.xml', SINGLE_BLOB) AS x[/quote]Ahh, I see. You loading the file in as I showed in the article, except that you're taking it straight to a variable. Then you're using that variable in sp_xml_preparedocument.Tue, 11 Jan 2011 10:18:31 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]SQL-DBA (1/11/2011)[/b][hr]XML is not going to work with HTML as content unless you enclose it with CDATA tags, like so...<Content> <![CDATA[ <div id="contentcenter"> <p>Perform a <b>hardware</b> check with the utility to assure the quality of the system driver, etc., etc.</p> </div> ]]></Content>Steve[/quote]Steve,Do you mean I need to go into the file and type in [CDATA[... where all HTML appears? AmyTue, 11 Jan 2011 09:34:00 GMTAmy.GRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxSorry for the delay guys: DECLARE @x XMLSELECT @x= CONVERT(xml, BulkColumn, 2) FROM OPENROWSET(BULK 'C:\data.xml', SINGLE_BLOB) AS xTue, 11 Jan 2011 09:28:09 GMTweharrelsonRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxYour sample mainly contains display information. Get the data in XML format (without the display information) and you're all set with Waynes article. Lutz, Thank you for that link on the difference btw xml and html. Exactly what I needed.There was actual data included in the xml file I was sent, but it was mixed in with all that other stuff. The person who sent me the file seemed pretty perplexed that I didn't know how to deal with it and load it into our database. With the comments I have received, I'm still not exactly sure.Tue, 11 Jan 2011 07:53:42 GMTAmy.GRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxXML is not going to work with HTML as content unless you enclose it with CDATA tags, like so...<Content> <![CDATA[ <div id="contentcenter"> <p>Perform a <b>hardware</b> check with the utility to assure the quality of the system driver, etc., etc.</p> </div> ]]></Content>SteveTue, 11 Jan 2011 04:24:42 GMTSQL-DBARE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]LutzM (1/10/2011)[/b][hr]The sample data you provided aren't XML data. Those are HTML data.[/quote]Agreed, but they didn't say that this was the entire data file...[quote][b]Amy.G (1/10/2011)[/b][hr]Question: I was given a table in XML format that had a lot of web formatting included, [b]such as:[/b]...[/quote] (my emphasis)If you read at the bottom of the post, then you might infer that the person was interpreting the HTML as XML, however...As such, I think this [b]is[/b] a valid question. [b]If[/b] an XML file were to contain such data, then how would this T-SQL handle it?Off the top of my head, I wouldn't expect T-SQL to treat HTML any differently from plain text...Tue, 11 Jan 2011 01:00:22 GMTKelsey ThorntonRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxLutz,Thanks for covering this!With both HTML and XML looking somewhat similar, it's easy to see why one would get confused over it.Mon, 10 Jan 2011 18:19:13 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxThe sample data you provided aren't XML data. Those are HTML data.As per [b][url]http://www.w3schools.com/Xml/xml_whatis.asp[/url][/b][quote]XML is not a replacement for HTML.XML and HTML were designed with different goals: * XML was designed to transport and store data, with focus on what data is * HTML was designed to display data, with focus on how data looksHTML is about displaying information, while XML is about carrying information.[/quote]Your sample mainly contains display information. Get the data in XML format (without the display information) and you're all set with Waynes article. ;-)Btw: what would be your expected output?Mon, 10 Jan 2011 16:06:31 GMTLutzMRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxThis article is very helpful, Thank you.Question: I was given a table in XML format that had a lot of web formatting included, such as:- <div id="content"> <a name="ada-content" /> - <div id="browseBox"> <div class="roundedBox_top-left" /> <div class="roundedBox_top-right" /> - <div class="licenseTitle">- <h1> <span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Title[1]">Aboveground Storage Tank (AST) Permits (>1 Million Gallons Total)</span> </h1>- <div class="licenseDescription"> <span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Description[1]">Minnesota Pollution Control Agency (MPCA)</span> </div> </div> <div class="roundedBox_bottom-buff" /> </div> <div class="roundedBox_bottom-left" /> <div class="roundedBox_bottom-right" /> - <div id="licenseDetail">- <div id="licenseRightColumn"> <h3 style="margin:0;line-height:100%;">Agency contact information:</h3> - <p>The table owner eventually sent me the data in Excel, but I've been wondering ever since if this is the type of XML data I SHOULD know how to work with. If so, how do you get around all those "round box" etc.?Mon, 10 Jan 2011 15:41:06 GMTAmy.GRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]Anil Maharjan (1/4/2011)[/b][hr]hello all,Well i am just wondering do this similar type of script works for the complex type node too for xml file.which we can find the XML file in link http://www.w3schools.com/schema/el_complextype.aspThanks Anil Maharjan[/quote]In most cases I can think of, it does. (A limitation for example would be the re-declaration of the same namespace within a document or the like...)But the link you provided is not an XML file. It's a schema definition that is used to validate an XML file.So you might need to do as Wayne recommended: start a new thread and post a sample XML you'd like to shred.Tue, 04 Jan 2011 09:53:06 GMTLutzMRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxAnil,I'm not sure. The link that you gave only gives partial examples; if you have a sample XML that does this, we can try it. I'm not even sure if SQL will work with this.I'd also suggest that you start a thread in the forums here for this, instead of tacking it on to this discussion of this article. You're more likely to get others involved to help you out.Tue, 04 Jan 2011 09:38:10 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxhello all,Well i am just wondering do this similar type of script works for the complex type node too for xml file.which we can find the XML file in link http://www.w3schools.com/schema/el_complextype.aspThanks Anil MaharjanTue, 04 Jan 2011 01:48:40 GMTAnil MaharjanRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]weharrelson (1/3/2011)[/b][hr]FWIW, We have weekly imports of ~25MB of XML. The sp_xml_preparedocument method ran circles around the new ways (x.data.value...)[/quote][url=http://msdn.microsoft.com/en-us/library/ms187367%28v=sql.90%29.aspx][u]BOL[/u][/url] says that sp_xml_preparedocument needs to have the xml text passed in to it... so as Jeff brought up, how do you get them from a file to a variable?Mon, 03 Jan 2011 19:10:57 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]weharrelson (1/3/2011)[/b][hr]FWIW, We have weekly imports of ~25MB of XML. The sp_xml_preparedocument method ran circles around the new ways (x.data.value...)[/quote]Care to share the snippet that imports those weekly [i]files[/i]?Mon, 03 Jan 2011 18:41:57 GMTJeff ModenRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxFWIW, We have weekly imports of ~25MB of XML. The sp_xml_preparedocument method ran circles around the new ways (x.data.value...)Mon, 03 Jan 2011 16:09:52 GMTweharrelsonRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]Geoff A (1/3/2011)[/b][hr]This is great info. Thanks Wayne.what do you think about making ALL x.data.values to be VARCHAR in the event that the XML is not well formatted?[/quote]For a standard way of doing things, I would not be in favour of doing that. Plus, it's not really XML if it's not well-formed, is it?Mon, 03 Jan 2011 14:12:19 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxThis is great info. Thanks Wayne.what do you think about making ALL x.data.values to be VARCHAR in the event that the XML is not well formatted?Mon, 03 Jan 2011 13:46:18 GMTGeoff ARE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]Steve Jones - SSC Editor (1/3/2011)[/b][hr]Excellent discussion, Wayne. This is one that I will keep bookmarked.[/quote][quote][b]mtillman-921105 (1/3/2011)[/b][hr]Excellent Wayne, thank you sir!:smooooth:[/quote][quote][b]gregg_dn (1/3/2011)[/b][hr]Thanks![/quote]Steve, Matt and Gregg - thanks for your kind words, and I'm very glad that you'll found this article so nice.Mon, 03 Jan 2011 12:10:29 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]Jeff Moden (1/3/2011)[/b][hr]I have to admit, I've not yet had to enter the realm of loading XML files. But now I know where to go for a quick reference. Thanks for filling in the cracks on this one, Wayne![/quote]You're welcome Jeff. Personally, I have to admit that it's pretty nice to be able to [i]finally[/i] teach you something! :-D (It's usually you teaching me! :w00t:)Mon, 03 Jan 2011 12:05:25 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]shad-873858 (1/3/2011)[/b][hr]Yes, as the author noted, you have to provide an alias to the OpenRowset. In his example the author used rs. This will get you past that particular error. Good luck.[/quote]Shad - thanks for covering for me.Mon, 03 Jan 2011 12:00:45 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]paul.marsh (1/3/2011)[/b][hr]decent article - good reference. but I feel a comment about the bcp utility should be added for those situations that warrant a different approach.[/quote]Paul - thanks. But how would you use BCP to load an XML file in? (This [i]is[/i] what this article is about...)Mon, 03 Jan 2011 11:58:52 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]Kelsey Thornton (1/3/2011)[/b][hr]Thanks for this.It's now clearer.[/quote]Kelsey,Good, I'm glad it all makes sense. If it hadn't, I would have failed.Mon, 03 Jan 2011 11:55:13 GMTWayneSRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxThanks!Mon, 03 Jan 2011 10:08:49 GMTgregg_dnRE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxExcellent Wayne, thank you sir!:smooooth:Mon, 03 Jan 2011 10:06:13 GMTmtillman-921105RE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspx[quote][b]gregg_dn (1/3/2011)[/b][hr]I'm getting an the following error when I try to run the OPENROWSET function:DECLARE @CD TABLE (XMLData XML);INSERT INTO @CDSELECT *FROM OPENROWSET(BULK N'C:\SQL\cd_catalog.xml', SINGLE_BlOB)Msg 491, Level 16, State 1, Line 5A correlation name must be specified for the bulk rowset in the from clause.Is there something in the code beyond SINGLE_BLOB) ?ThanksG. Daniels[/quote]Yes, as the author noted, you have to provide an alias to the OpenRowset. In his example the author used rs. This will get you past that particular error. Good luck.Mon, 03 Jan 2011 09:57:03 GMTshad-873858RE: Loading XML Data into SQL Server (SQL Spackle)http://www.sqlservercentral.com/Forums/Topic1041547-1273-1.aspxI'm getting an the following error when I try to run the OPENROWSET function:DECLARE @CD TABLE (XMLData XML);INSERT INTO @CDSELECT *FROM OPENROWSET(BULK N'C:\SQL\cd_catalog.xml', SINGLE_BlOB)Msg 491, Level 16, State 1, Line 5A correlation name must be specified for the bulk rowset in the from clause.Is there something in the code beyond SINGLE_BLOB) ?ThanksG. DanielsMon, 03 Jan 2011 09:16:33 GMTgregg_dn