[es] - Citanje iz XML fajla

Car78

Član broj: 59313
Poruke: 20
217.75.193.*

Profil

^{01.06.2007. u 08:39 - pre 205 meseci}

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.

Odgovor na temu

dekibre
Dejan Mladenovic
Oslo, Norveska

Član broj: 21820
Poruke: 246
84.236.124.*

Sajt: dekibre.on.w802.net/index..

+4 Profil

Re: Citanje iz XML fajla

^{01.06.2007. u 12:51 - pre 205 meseci}

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>

You can fool some people sometimes,
But you can't fool all the people all the time. (Bob Marley)

Odgovor na temu