January 30, 2006 at 6:07 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/smoore/importingxmlfilesintosqlserver.asp
February 15, 2006 at 6:38 am
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.
February 15, 2006 at 7:49 am
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).
February 16, 2006 at 2:29 am
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.
November 23, 2007 at 1:28 am
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
November 23, 2007 at 5:24 am
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.
November 23, 2007 at 7:24 am
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.
November 23, 2007 at 11:17 am
Why not just write an XQuery to import the data. It works fine
November 24, 2007 at 8:58 pm
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
November 26, 2007 at 7:15 am
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...
March 1, 2009 at 11:48 pm
[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]
June 29, 2009 at 12:27 am
hey if u got d answer plz share with us.
thanks.
February 23, 2010 at 4:40 am
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 xml
SELECT @xml = BulkColumn
/* select * */
from openrowset
(
bulk 'c:\testout\stream.xml',SINGLE_BLOB
)T
select @xml
<<<<< This works also to do the same thing ... >>>>
create table #workingtable (data xml)
insert into #workingtable
select *
from openrowset (bulk 'c:\testout\stream.xml',SINGLE_BLOB) as data
select * from #workingtable
declare @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?
or
use 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
February 24, 2010 at 12:55 am
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 XML
2) Parse it into variables with XQuery
3) Insert it into the database
Steve
Note: 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 service
Dim m_datasvc As New clsws_DataService
Dim i As Long
Dim tbToken As String
' Summary data
Dim s_xml As MSXML2.IXMLDOMNodeList
Dim s_node As MSXML2.IXMLDOMNode
Dim s_cnt As String
Dim s_sql As String
Dim s_sqldelete As String
' Error data
Dim e_sql As String
' tbToken is the id returned from the web service Authenticate method
If IsNull(Me.tbAuthToken.Value) Then
tbToken = ""
Else
tbToken = Me.tbAuthToken.Value
End If
If (Me.chkSummaryData.Value = 0 And Me.chkDetailData.Value = 0) Then
MsgBox "You did not chose a dataset to update.", vbOKOnly
GoTo Exit_btnUpdateData_Click
End If
If tbToken = "" Then
MsgBox "You did not enter an authentication token.", vbOKOnly
GoTo Exit_btnUpdateData_Click
End If
DoCmd.Hourglass True
' Create an instance of the web service
Set m_datasvc = New clsws_DataService
'****************************************
' SUMMARY DATA
'****************************************
If Me.chkSummaryData.Value = -1 Then
Set 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 table
DoCmd.SetWarnings False
s_sqldelete = "DELETE FROM VWGETDATA"
DoCmd.RunSQL (s_sqldelete)
Dim s_COUNTRY_NAME
Dim s_ISO_CODE
Dim s_MEASUREMENT_STRING
Dim s_MEASUREMENT_NUMERIC
Dim s_YEAR
Dim s_ID
Dim s_MEASURE_NAME
Dim s_REGION_NAME
If Not s_xml Is Nothing Then
i = 0
For 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 If
End If
' End data processing
DoCmd.SetWarnings True
DoCmd.Hourglass False
MsgBox ("Update complete!")
DoCmd.Close
End Sub
February 28, 2010 at 9:13 pm
thanks Steve
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply