Importing XML Files Into SQL Server

  • SQL-DBA

    Hall of Fame

    Points: 3004

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

  • Attinder Singh

    SSC Journeyman

    Points: 77

    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

    SSCarpal Tunnel

    Points: 4348

    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

    Hall of Fame

    Points: 3004

    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

    SSC Enthusiast

    Points: 147

    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

    SSC Journeyman

    Points: 91

    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

    Hall of Fame

    Points: 3004

    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

    SSC Enthusiast

    Points: 104

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

  • Stu Boy

    SSC Enthusiast

    Points: 147

    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

    SSC Journeyman

    Points: 79

    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...

  • arlene_jethro

    SSC Rookie

    Points: 35

    [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]

  • pawankalakoti

    Grasshopper

    Points: 19

    hey if u got d answer plz share with us.

    thanks.

  • adman3613

    SSC Veteran

    Points: 273

    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

  • SQL-DBA

    Hall of Fame

    Points: 3004

    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

  • adman3613

    SSC Veteran

    Points: 273

    thanks Steve

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply