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
arlene_jethro
arlene_jethro
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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...Smile

pawankalakoti
pawankalakoti
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
hey if u got d answer plz share with us.
thanks.
adman3613
adman3613
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 76
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
SQL-DBA
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 463
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
adman3613
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 76
thanks Steve
adman3613
adman3613
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 76
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???
adman3613
adman3613
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 76
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 ...
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23953 Visits: 13559
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
adman3613
adman3613
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 76
OMG .... unbelievable .... went from 59sec for 300 records to < 1sec ... wow ... thanks a million ...
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23953 Visits: 13559
Glad I could help :-D
And sorry for underestimating the performance improvement ;-)



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