SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing XML Files Into SQL Server


Importing XML Files Into SQL Server

Author
Message
SQL-DBA
SQL-DBA
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 462
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/smoore/importingxmlfilesintosqlserver.asp
Attinder Singh
Attinder Singh
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 8
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.
Peter Kryszak
Peter Kryszak
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1352 Visits: 3
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).



SQL-DBA
SQL-DBA
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 462

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.


Stu Boy
Stu Boy
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 74
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
dmd dmd
dmd dmd
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 56
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.
SQL-DBA
SQL-DBA
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 462
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.
pxwarner
pxwarner
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 63
Why not just write an XQuery to import the data. It works fine



Stu Boy
Stu Boy
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 74
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
Andy DIllbeck
Andy DIllbeck
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 19
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search