Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Importing XML Files Into SQL Server Expand / Collapse
Author
Message
Posted Monday, January 30, 2006 6:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:13 PM
Points: 80, Visits: 331
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/smoore/importingxmlfilesintosqlserver.asp
Post #254592
Posted Wednesday, February 15, 2006 6:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 11, 2009 11:56 AM
Points: 15, Visits: 4
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.
Post #258615
Posted Wednesday, February 15, 2006 7:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 7:51 PM
Points: 1,244, 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).



Post #258660
Posted Thursday, February 16, 2006 2:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:13 PM
Points: 80, Visits: 331

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. 

Post #258947
Posted Friday, November 23, 2007 1:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 12:45 AM
Points: 9, 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
Post #425169
Posted Friday, November 23, 2007 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 1, Visits: 41
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.
Post #425243
Posted Friday, November 23, 2007 7:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:13 PM
Points: 80, Visits: 331
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.
Post #425275
Posted Friday, November 23, 2007 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 9, 2010 9:12 AM
Points: 4, Visits: 63
Why not just write an XQuery to import the data. It works fine


Post #425337
Posted Saturday, November 24, 2007 8:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 12:45 AM
Points: 9, 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
Post #425465
Posted Monday, November 26, 2007 7:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 1:15 PM
Points: 5, 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...
Post #425748
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse