import complex?? xml file into relational table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

import complex?? xml file into relational table

Author

Topic

adman3613
Starting Member

1 Post

Posted - 2010-02-23 : 16:24:20

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 09:37:56

do you mean you need to extract values from xml into table fields? if yes, use OPENXML or new xml methods like nodes(),query()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-02-24 : 12:16:33

Here are some examples of what Visakh mentioned.

The query plans seem to suggest that the OPENQUERY method is a lot more efficient.
I have no experience of large volumes of XML data, so this may not be the case with a 300k file.

OPENXML eg


DECLARE @idoc int
DECLARE @xml xml
SET @xml =
'<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>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT *
FROM OPENXML (@idoc, '//Circuits/Circuit',1)
	WITH
	(
		CircuitName varchar(10) '@Name'
		,Adrs varchar(20)
		,G varchar(20)
		,Ofst varchar(20)
		,Micro varchar(20)
		-- etc
	)
 
SELECT *
FROM OPENXML (@idoc, '/Circuits/Circuit/Prg',1)
	WITH
	(
		CircuitName varchar(20) '../@Name'
		,PrgName varchar(20) '@Name'
		,Indx int
		,AcummAH varchar(20)
	) 

SELECT *
FROM OPENXML (@idoc, '/Circuits/Circuit/Prg/Stp',1)
	WITH
	(
		CircuitName varchar(20) '../../@Name'
		,PrgName varchar(20) '../@Name'
		,StpID int '@ID'
		,Cod int
		,N2 decimal(6,1)
		,T12 decimal(6,1)
	)

value() eg:


DECLARE @xml xml
SET @xml =
'<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>'

SELECT R.value('@Name[1]', 'varchar(20)') As CircuitName
	,R.value('@Adrs[1]', 'varchar(20)') As Adrs
	,R.value('@G[1]', 'varchar(20)') As G
	,R.value('@Ofst[1]', 'varchar(20)') As Ofst
	,R.value('@Micro[1]', 'varchar(20)') As Micro
	-- etc
FROM @xml.nodes('/Circuits/Circuit') R(R)

SELECT R.value('../@Name[1]', 'varchar(20)') As CircuitName
	,R.value('@Name[1]', 'varchar(20)') As PrgName
	,R.value('@Indx[1]', 'int') As Indx
	,R.value('@AcummAH[1]', 'varchar(20)') As AcummAH
FROM @xml.nodes('/Circuits/Circuit/Prg') R(R)

SELECT R.value('../../@Name[1]', 'varchar(20)') As CircuitName
	,R.value('../@Name[1]', 'varchar(20)') As PrgName
	,R.value('@ID[1]', 'int') As StpID
	,R.value('@Cod[1]', 'int') As Cod
	,R.value('@N2[1]', 'decimal(6,1)') As N2
	,R.value('@T12[1]', 'decimal(6,1)') As T12
FROM @xml.nodes('/Circuits/Circuit/Prg/Stp') R(R)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 12:37:55

and here's another one from recent thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140392

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources