SQLServerCentral / Discuss Content Posted by Steve Moore / Article Discussions / Article Discussions by Author / Importing XML Files Into SQL Server / Latest Posts

RE: Importing XML Files Into SQL Server

emzi19 — Fri, 06 Aug 2010 05:44:52 GMT

Hi there,I tried using this article but i get an object required error on the lineSet wsh = WScript.CreateObject("WScript.Shell") ?

RE: Importing XML Files Into SQL Server

ani.swath — Tue, 22 Jun 2010 05:25:35 GMT

How to save xml file data into sql server database by using vb.net

RE: Importing XML Files Into SQL Server

adman3613 — Mon, 15 Mar 2010 19:27:51 GMT

another issue just came up ....i have come across some xml files that have the following: -- <Circuit Name="13B-10" Adrs="710" G="13B" Ofst="1" Micro="0" SvrID="0" ProcID="727842327" PrgIndx="641" PrgStp="2" Mode="7" A="37.91" StpAH="96.6" V="18.7" Tmp="32.0" StpT="152" RunTime="184" Comm="0" Tick="4095" Pwr="True" MainC="" ES="100" TLeft="392.0582" AccumAH="113.616" -Alarm="0" ProcStart="2/16/2010 9:17:34 PM" ProcEnd="12:00:00 AM" ProcInfo="" -ProcFree="12:00:00 AM" -TIA="0" TIV="0" TIT="0" Cyc="0" AT="0" CR="0" TCR="0">- <Prg Name="269PMPG" Indx="641" AcummAH="False">- <Stp ID="1" Cod="7" N2="34.1" T12="17"></Stp> - <Stp ID="2" Cod="7" N2="40" T12="221"></Stp> - <Stp ID="3" Cod="7" N2="34.1" T12="40.9"></Stp> - <Stp ID="4" Cod="7" N2="25.5" T12="52.6"></Stp> - <Ext>- - </Ext>- </Prg>- <UData></UData>- </Circuit>-</Circuits> <Stp ID="2" Cod="7" N2="40" T12="221"></Stp> - <Stp ID="3" Cod="7" N2="34.1" T12="40.9"></Stp> - <Stp ID="4" Cod="7" N2="25.5" T12="52.6"></Stp> - <Ext>- - </Ext>- </Prg>- <UData></UData>- </Circuit>-</Circuits>1" Cod="7" N2="40" T12="527.3"></Stp> - <Ext>- - </Ext>- </Prg>- <UData></UData>- </Circuit>-</Circuits>ircuits>ts>s>ts>cuits>"></Stp> - <Ext>- - </Ext>- </Prg>- <UData></UData>- </Circuit>-</Circuits>don't mind the "-" as this what i did to post the text in the forum. The first element of -<Circuit> is fine as you can see but below you can see where the file has gotten corrupted and there is a bunch of -</Circuits> ... some are even incomplete within the < identifiers. I would like to truance the bulk load at the end of the good data. This is giving me a sql server first exception error.

RE: Importing XML Files Into SQL Server

LutzM — Mon, 15 Mar 2010 11:11:55 GMT

Glad I could help :-DAnd sorry for underestimating the performance improvement ;-)

RE: Importing XML Files Into SQL Server

adman3613 — Sun, 14 Mar 2010 23:33:02 GMT

OMG .... unbelievable .... went from 59sec for 300 records to < 1sec ... wow ... thanks a million ...

RE: Importing XML Files Into SQL Server

LutzM — Sat, 13 Mar 2010 10:31:08 GMT

The reason for the bad performance is you're 'climbing up' one level each time you select one of the values from the Circuit node. Try to use CROSS APPLY instead.I'd guess it's at least 10 times faster with your data than your current code...[code="sql"]insert into [MiddletownData].[dbo].[RectifierData]select U.Circuits.value('@Name', 'nvarchar(10)') as CircuitName,U.Circuits.value('@ProcID', 'nchar(10)') as ProcID,U.Circuits.value('@PrgStp', 'int') as PrgStp,U.Circuits.value('@Mode', 'int') as Mode,U.Circuits.value('@A', 'float') as Amps,U.Circuits.value('@StpAH', 'float') as StpAH,U.Circuits.value('@V', 'float') as Volts,U.Circuits.value('@StpT', 'float') as StpT,U.Circuits.value('@RunTime', 'int') as RunTime,U.Circuits.value('@Tick', 'nchar(4)') as Tick,U.Circuits.value('@Pwr', 'char(5)') as Pwr,U.Circuits.value('@TLeft', 'float') as TLeft,U.Circuits.value('@AccumAH', 'float') as AccumAH,U.Circuits.value('@Alarm', 'nchar(2)') as Alarm,U.Circuits.value('@ProcStart', 'Datetime') as ProcStart,U.Circuits.value('@ProcEnd', 'Datetime') as ProcEnd,U.Circuits.value('@ProcFree', 'Datetime') as ProcFree,T.Circuit.value('@Name', 'nvarchar(15)') as PrgNamefrom @data.nodes('Circuits/Circuit') as U(Circuits)cross apply Circuits.nodes('Prg') T(Circuit)[/code]

RE: Importing XML Files Into SQL Server

adman3613 — Sat, 13 Mar 2010 04:41:56 GMT

I don't know where my head is .... lol ...here is the query i am using to do this declare @data xmlSELECT @data = BulkColumn /* select * */from openrowset(bulk 'c:\testout\stream.xml',SINGLE_BLOB)Tinsert into [MiddletownData].[dbo].[RectifierData]select T.Circuit.value('../@Name', 'nvarchar(10)') as CircuitName, T.Circuit.value('../@ProcID', 'nchar(10)') as ProcID, T.Circuit.value('../@PrgStp', 'int') as PrgStp, T.Circuit.value('../@Mode', 'int') as Mode, T.Circuit.value('../@A', 'float') as Amps, T.Circuit.value('../@StpAH', 'float') as StpAH, T.Circuit.value('../@V', 'float') as Volts, T.Circuit.value('../@StpT', 'float') as StpT, T.Circuit.value('../@RunTime', 'int') as RunTime, T.Circuit.value('../@Tick', 'nchar(4)') as Tick, T.Circuit.value('../@Pwr', 'char(5)') as Pwr, T.Circuit.value('../@TLeft', 'float') as TLeft, T.Circuit.value('../@AccumAH', 'float') as AccumAH, T.Circuit.value('../@Alarm', 'nchar(2)') as Alarm, T.Circuit.value('../@ProcStart', 'Datetime') as ProcStart, T.Circuit.value('../@ProcEnd', 'Datetime') as ProcEnd, T.Circuit.value('../@ProcFree', 'Datetime') as ProcFree, T.Circuit.value('@Name', 'nvarchar(15)') as PrgNamefrom @data.nodes('Circuits/Circuit/Prg') as T(Circuit);using the same xml file I posted with my first msg ...

RE: Importing XML Files Into SQL Server

adman3613 — Sat, 13 Mar 2010 04:38:59 GMT

Thanks again Steve ... i did something a little different but it helped all the same ... I was pretty beat up by this until your reply ...Now ... this is slightly off topic with this current discussion but ....I have this running right now by hand as a query that I run from Management Studio. I will be calling it from my vb ap that's doing all the other background file work. Right now I have about 30 records per xml file and it takes 58 sec to read, parse, and load into my sql table. When this gets automated it will be receiving a new file about every 60sec. I really need to get this time down. Getting it to the 10 sec range would be where I am in need of if possible.any thoughts???

RE: Importing XML Files Into SQL Server

adman3613 — Sun, 28 Feb 2010 21:13:49 GMT

thanks Steve

RE: Importing XML Files Into SQL Server

SQL-DBA — Wed, 24 Feb 2010 00:55:24 GMT

Well, I am not really sure this will help because it is an example of inserting an XML web service into an Access table in VBA, but the approach is roughly the same, meaning:1) Specify the location of your XML2) Parse it into variables with XQuery3) Insert it into the databaseSteveNote: Exit and Error handling removed from example. Also, this goes into a flat table. You will either need to flatten your data import table or put multiple node values in a different table.Private Sub btnUpdateData_Click()On Error GoTo Err_btnUpdateData_Click'Define vars and web serviceDim m_datasvc As New clsws_DataServiceDim i As LongDim tbToken As String' Summary dataDim s_xml As MSXML2.IXMLDOMNodeListDim s_node As MSXML2.IXMLDOMNodeDim s_cnt As StringDim s_sql As StringDim s_sqldelete As String' Error dataDim e_sql As String' tbToken is the id returned from the web service Authenticate methodIf IsNull(Me.tbAuthToken.Value) Then tbToken = ""Else tbToken = Me.tbAuthToken.ValueEnd IfIf (Me.chkSummaryData.Value = 0 And Me.chkDetailData.Value = 0) ThenMsgBox "You did not chose a dataset to update.", vbOKOnlyGoTo Exit_btnUpdateData_ClickEnd IfIf tbToken = "" ThenMsgBox "You did not enter an authentication token.", vbOKOnlyGoTo Exit_btnUpdateData_ClickEnd IfDoCmd.Hourglass True' Create an instance of the web serviceSet m_datasvc = New clsws_DataService'****************************************' SUMMARY DATA'****************************************If Me.chkSummaryData.Value = -1 ThenSet s_xml = m_datasvc.wsm_GetWhsData(tbToken).Item(0).selectNodes("//Data/Table")s_cnt = s_xml.length If s_cnt <= 0 Then MsgBox "The summary data web service is not available so no changes will be made to data.", vbOKOnly GoTo Exit_btnUpdateData_Click Else MsgBox ("You are about to update " & s_cnt & " records (summary data)! Please be patient as this can take quite a long time (over an hour in some cases)."), vbOKCancel End If' If update summary data is checked then delete everything from the table then get data' from the web service and insert it into the summary tableDoCmd.SetWarnings Falses_sqldelete = "DELETE FROM VWGETDATA"DoCmd.RunSQL (s_sqldelete)Dim s_COUNTRY_NAMEDim s_ISO_CODEDim s_MEASUREMENT_STRINGDim s_MEASUREMENT_NUMERICDim s_YEARDim s_IDDim s_MEASURE_NAMEDim s_REGION_NAMEIf Not s_xml Is Nothing Theni = 0For Each s_node In s_xml If i <= s_cnt Then s_COUNTRY_NAME = s_node.selectNodes("//COUNTRY_NAME").Item(i).Text s_ISO_CODE = s_node.selectNodes("//Table/ISO_CODE").Item(i).Text s_MEASUREMENT_STRING = s_node.selectNodes("//Table/MEASUREMENT_STRING").Item(i).Text s_MEASUREMENT_NUMERIC = s_node.selectNodes("//Table/MEASUREMENT_NUMERIC").Item(i).Text s_YEAR = s_node.selectNodes("//Table/YEAR").Item(i).Text s_ID = s_node.selectNodes("//Table/ID").Item(i).Text s_MEASURE_NAME = s_node.selectNodes("//Table/MEASURE_NAME").Item(i).Text s_REGION_NAME = s_node.selectNodes("//Table/REGION_NAME").Item(i).Text s_COUNTRY_NAME = Replace(s_COUNTRY_NAME, "'", "''") s_MEASUREMENT_STRING = Replace(s_MEASUREMENT_STRING, "'", "''") s_MEASURE_NAME = Replace(s_MEASURE_NAME, "'", "''") s_sql = "INSERT INTO VWGETDATA (COUNTRY_NAME,ISO_CODE,MEASUREMENT_STRING,MEASUREMENT_NUMERIC,YEAR,ID,MEASURE_NAME,REGION_NAME) VALUES ('" & s_COUNTRY_NAME & "','" & s_ISO_CODE & "','" & s_MEASUREMENT_STRING & "'," & s_MEASUREMENT_NUMERIC & "," & s_YEAR & "," & s_ID & ",'" & s_MEASURE_NAME & "','" & s_REGION_NAME & "') ;" DoCmd.RunSQL (s_sql) End If i = i + 1 Next s_node End IfEnd If' End data processingDoCmd.SetWarnings TrueDoCmd.Hourglass FalseMsgBox ("Update complete!")DoCmd.CloseEnd Sub

RE: Importing XML Files Into SQL Server

adman3613 — Tue, 23 Feb 2010 04:40:54 GMT

I'm a newbe ..... I have about 30 tables in my MsSql 2005 database. I'm using VS management Studio 2005. I have about 20 queries that i have written. I need to import a xml file into one of my tables. I have a application i've written in vb.net that will either call a proceedure or actually handle the importing it's self. Every minute i get a file (stream.xml) from another system and need to import it's data. I have created the table and tried to get the data in but am getting stuck due to lack of knowledge in xml and sql. Below is a snippet of the xml file.- <Circuits>- <Circuit Name="101" Adrs="101" G="1T" Ofst="1" Micro="0" SvrID="0" ProcID="1621103984" PrgIndx="10" PrgStp="1" Mode="0" A="0.23" StpAH="341.8" V="0.0" Tmp="407.1" StpT="0" RunTime="1025" Comm="0" Tick="0" Pwr="True" MainC="" ES="100" TLeft="0" AccumAH="341.804" Alarm="0" ProcStart="1/26/2010 9:04:25 PM" ProcEnd="1/27/2010 2:07:54 PM" ProcInfo="" ProcFree="1/27/2010 2:31:50 PM" TIA="0" TIV="0" TIT="0" Cyc="0" AT="0" CR="0" TCR="0">- <Prg Name="292P" Indx="10" AcummAH="False"> <Stp ID="1" Cod="7" N2="20" T12="341.8" /> <Ext /> </Prg> <UData /> </Circuit>- <Circuit Name="13B-06" Adrs="706" G="13B" Ofst="1" Micro="0" SvrID="0" ProcID="1566143250" PrgIndx="534" PrgStp="1" Mode="7" A="38.99" StpAH="1.9" V="19.7" Tmp="32.0" StpT="8" RunTime="8" Comm="0" Tick="3189" Pwr="True" MainC="" ES="100" TLeft="491.0146" AccumAH="1.87" Alarm="0" ProcStart="1/28/2010 3:45:39 AM" ProcEnd="12:00:00 AM" ProcInfo="" ProcFree="12:00:00 AM" TIA="0" TIV="0" TIT="0" Cyc="0" AT="0" CR="0" TCR="0">- <Prg Name="224PMPG" Indx="534" AcummAH="False"> <Stp ID="1" Cod="7" N2="39.1" T12="19.6" /> <Stp ID="2" Cod="7" N2="40" T12="243.3" /> <Stp ID="3" Cod="7" N2="39.1" T12="47" /> <Stp ID="4" Cod="7" N2="29.4" T12="13.1" /> <Ext /> </Prg> <UData /> </Circuit> </Circuits>I have tried numerous examples where i created their xml example file, copied their code, and ran as a proceedure all worked using their examples. I've tried Bulk load to a @xml and #xml with openrowset, OpenXML, and even tried creating a dataset in my vb forms app but all to no avail. I'm missing something ... obviously ...:-)this works getting all into one record in a table...declare @xml xmlSELECT @xml = BulkColumn /* select * */from openrowset(bulk 'c:\testout\stream.xml',SINGLE_BLOB)Tselect @xml<<<<< This works also to do the same thing ... >>>> create table #workingtable (data xml)insert into #workingtableselect *from openrowset (bulk 'c:\testout\stream.xml',SINGLE_BLOB) as dataselect * from #workingtabledeclare @xml as xml,@name as nvarchar(15)select @xml = data from #workingtable<<<< >>>>>I think I want to use the first ... right ...as my files are never over 300k in size ....I'm getting lost on what's after this ... direct mapping in the proceedure ? how?oruse a schema? how?or parse using Vb ...you get the idea ....btw i want this saved as relational data instead of xml typed data ... any help would be greatly appreciated

RE: Importing XML Files Into SQL Server

pawankalakoti — Mon, 29 Jun 2009 00:27:04 GMT

hey if u got d answer plz share with us.thanks.

RE: Importing XML Files Into SQL Server

arlene_jethro — Sun, 01 Mar 2009 23:48:12 GMT

[font="Comic Sans MS"]Hello.. can anyone help me on how to import a certain .xml file to be saved in the database using C#... I know how to import a .csv file (by using schema.ini), but how about importing .xml file to sql server... I really had a hard time thinking about the code.. any help will be greatly appreciated...:)[/font]

RE: Importing XML Files Into SQL Server

Andy DIllbeck — Mon, 26 Nov 2007 07:15:26 GMT

What if you wanted to send stuff to multiple tables? <recallitem> <RecallNum></RecallNum> <Description></Description> <Expires></Expires> <DealerTypeID>1</DealerTypeID> <Items> <RecallItems> <Correction></Correction> <LaborOp></LaborOp> <Labor></Labor> </RecallItems> <RecallItems> <Correction></Correction> <LaborOp></LaborOp> <Labor></Labor> </RecallItems> <RecallItems> <Correction></Correction> <LaborOp></LaborOp> <Labor></Labor> </RecallItems> </Items> <RecallExplain> <Explaination></Explaination> </RecallExplain> </recallitem>Where you can have multiple recallitems and recallexplain tags. My solution was to write something in vb.net to loop through these subitems and insert them in to seperate tables. Just asking because someone else may run into this problem...

RE: Importing XML Files Into SQL Server

Stu Boy — Sat, 24 Nov 2007 20:58:21 GMT

Admittedly I haven't tested it, but from what I understand it wouldn't be appropriate to use XQuery in our situation. The performance on importing files potentially over a gigabyte, wouldn't cut it compared to using sqlxmlbulkload. I have tested the sqlxmlbulkload process with XML files upto 3.5GB and it works very happily and seemingly efficiently on an overworked and underspec'ed virtual dev box with 1 GB of virtual memory [sql2005 and the import process running on the same box].Stu

RE: Importing XML Files Into SQL Server

pxwarner — Fri, 23 Nov 2007 11:17:23 GMT

Why not just write an XQuery to import the data. It works fine

RE: Importing XML Files Into SQL Server

SQL-DBA — Fri, 23 Nov 2007 07:24:16 GMT

Stu Boy -Sounds like a waste of time to me. Security should be set up at the server/database level and at the folder/file level. I did something similar with a C# program that opened SOAP messages then inserted into MSSQL. I am not sure that gets you anymore security though.

RE: Importing XML Files Into SQL Server

dmd dmd — Fri, 23 Nov 2007 05:24:11 GMT

First, excuseme by my english, i was whit huge problem to import xml to sqlserver, i try microsoft schemas, but the better solution i found was a apliccation xml2cvs (http://www.programurl.com/a7soft-xml2csv.htm), i created a vbscript to call xml2csv and after import csv to sqlserver using a DTS to make operation.

RE: Importing XML Files Into SQL Server

Stu Boy — Fri, 23 Nov 2007 01:28:56 GMT

We're doing something like this using sqlbulkload in a c# exe for an ETL from our transactional database to sql05. Thousands of rows every few hours converted to XML there then into SQL running flawlessly, periodically as a scheduled job. Our Tech Service guys want us to run this ETL in an SSIS package - for security[?]. Should we do it or is it a waste of energy?If it's a good idea - how would we go about setting it up?Ta Stu

RE: Importing XML Files Into SQL Server

SQL-DBA — Thu, 16 Feb 2006 02:29:00 GMT

The essential question is whether or not to read this fairly simple data from an XML file or from a database. Prior to implementing a new FAQ system, the data were read from the XML file exactly as Peter suggests:

reader = New XmlTextReader(url)

Dim questionObj As Object = reader.NameTable.Add("Question") Dim answerObj As Object = reader.NameTable.Add("Content") Dim summaryObj As Object = reader.NameTable.Add("Summary")

Do While reader.Read()

If reader.NodeType = XmlNodeType.Element Then

If reader.Name.Equals(questionObj) Then

Dim content As String = reader.ReadInnerXml

content = content.Remove(0, 4) content = content.Remove(content.Length - 4, 4) Me._title = Me.ParseText(content) template = template.Replace("$Question$", Me._title) template = template.Replace("$FileID$", fileID)

End If

Etc, etc, etc....

The business problem that I faced was that a new system stored this same data in a db; So, I have "old" data in XML files and "new" data in a db. Hence, the need to import the XML files into SQL Server.

RE: Importing XML Files Into SQL Server

Peter Kryszak — Wed, 15 Feb 2006 07:49:00 GMT

It seems to me that the original XML is malformed by any definition and should have used CDATA or escaped the "content" in the first place.It's been my experience in working with web services that using CDATA is being deprecated and that escaping the inner XML is the way that is gaining acceptance. In fact, we had serious issues when a vendor used CDATA, but once the vendor escaped it, the inner XML was handled automatically by .NET classes (and I think this is true of some of the Java-based frameworks too).

RE: Importing XML Files Into SQL Server

Attinder Singh — Wed, 15 Feb 2006 06:38:00 GMT

This is an good article but missed 1 thing, if want to do xml bulk import from .net, then your application should be single threaded or you need to define thread as STA thread, then only you will be able to import, otherwise it throws error.

Importing XML Files Into SQL Server

SQL-DBA — Mon, 30 Jan 2006 18:07:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/smoore/importingxmlfilesintosqlserver.asp