Importing XML Files Into SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Steve Moore » Importing XML Files Into SQL Server

23 posts, Page 2 of 3

««1 2 3 »»

Importing XML Files Into SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

arlene_jethro

Posted Sunday, March 01, 2009 11:48 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 11, 2009 8:42 PM
Points: 1, Visits: 7

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

Post #666371

pawankalakoti

Posted Monday, June 29, 2009 12:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 29, 2009 12:24 AM
Points: 1, Visits: 1

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

Post #743382

adman3613

Posted Tuesday, February 23, 2010 4:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:23 PM
Points: 6, Visits: 64

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

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

Post #871039

SQL-DBA

Posted Wednesday, February 24, 2010 12:55 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:37 PM
Points: 73, Visits: 273

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

Post #871760

adman3613

Posted Sunday, February 28, 2010 9:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:23 PM
Points: 6, Visits: 64

thanks Steve

Post #874294

adman3613

Posted Saturday, March 13, 2010 4:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:23 PM
Points: 6, Visits: 64

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

Post #882341

adman3613

Posted Saturday, March 13, 2010 4:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:23 PM
Points: 6, Visits: 64

I don't know where my head is .... lol ...

here is the query i am using to do this

declare @data xml

SELECT @data = BulkColumn
/* select * */
from openrowset
(
bulk 'c:\testout\stream.xml',SINGLE_BLOB
)T

insert 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 PrgName

from @data.nodes('Circuits/Circuit/Prg') as T(Circuit);

using the same xml file I posted with my first msg ...

Post #882342

LutzM

Posted Saturday, March 13, 2010 10:31 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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

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 PrgName

from @data.nodes('Circuits/Circuit') as U(Circuits)
cross apply Circuits.nodes('Prg') T(Circuit)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #882388