Citanje iz XML fajla

[ Car78 @ 01.06.2007. 08:39 ] @

Pozdrav
Proces importa podataka iz nekog XML fajla
Pitanje: Imam situaciju da se podaci nalaze u nekom XML fajlu i potrebno ih je imortovati na server. Dali postoji mogućnost da napišem neku proceduru koja bi prihvatala ime fajla i lokaciju, koja bi se pokrenula i odradila import.

[ dekibre @ 01.06.2007. 12:51 ] @

Evo ti kod sa kojim sam se ja malo igrao pa ti probaj da vidiš da li ti nešto od ovoga koristi.

Code:
declare @XMLfile varchar(100)

declare @idoc int
declare @doc varchar(8000)
declare @xml_line varchar(200)
declare @bulkinscmd varchar(500)

set @XMLfile = 'C:\App\student.xml'
set @doc =''

drop table #tempXML
-- insert XML file into temporary table
Create table #tempXML
(line varchar(8000))

set @bulkinscmd = 'BULK INSERT #tempXML FROM ' +
'''' + @XMLfile + ''''
exec (@bulkinscmd)

declare @row varchar(500)

declare @count int

select @count = count(*) from #tempXML

while @count > 0
begin

select top 1 @row =line from #tempXML

-- select @row

set @doc = @doc + rtrim(ltrim(@row))

delete from #tempXML where line like @row

select @count = @count - 1
--print @count

end

set @doc = substring(@doc, 4, 1000)

select @doc

exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement using OPENXML rowset provider
SELECT text
FROM OPENXML (@idoc, '/Data')
WHERE text is not null
EXEC sp_xml_removedocument @idoc

xml file koji sam koristio u primeru

Code:
<?xml version="1.0"?>
<Data>
  <Student id = "s1" name = "Student1"  attends = "c1 c3 c6"  />
  <Student id = "s2" name = "Student2"  attends = "c2 c4" />
  <Student id = "s3" name = "Student3"  attends = "c2 c4 c6" />
  <Student id = "s4" name = "Student4"  attends = "c1 c3 c5" />
  <Student id = "s5" name = "Student5"  attends = "c1 c3 c5 c6" />
  <Student id = "s6" name = "Student6" />
</Data>