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