Import XML file to database (MS Server 2000) using procedure (transact sql) ???
I must import some exemplary file to database (MS Srrver 2000) ofcourse
using procedure Transact SQL.
This file must:
1.Read the xml file
2. Create table
3. Import this date from xml file to my database
Ps. I create procedure who File xml imports to base, but unfortunately she
only schedule when earlier create a table or table is created.So I need (I
think) create such mini parser in language transact SQL.
Does someone have some ideas?
For every help Thanks
==== example file xml ===========================================
<root>
<Cust>
<IDosoby>1</IDosoby>
<Imie>Lukasz</Imie>
<Nazwisko>Przypadek</Nazwisko>
</Cust>
<Cust>
<IDosoby>2</IDosoby>
<Imie>Dariusz </Imie>
<Nazwisko>Mroz</Nazwisko>
</Cust>
<Cust>
<IDosoby>3</IDosoby>
<Imie>Tomasz</Imie>
<Nazwisko>Kolo</Nazwisko>
</Cust>
</root>
===========================================================
--
Luk
ljag
comp.databases.ms-sqlserver
14567 articles. 
1 followers.
janinepelletier (108) is leader.
Post
Follow
3 Replies
1087 Views
Similar Articles[PageSpeed] 50
Not sure of the exact requirement. But if you can parse the XML and then using OPENXML shred it into a new table. SELECT * INTO <MyNewTable> FROM OPENXML (@idoc, '/root/') Where @idoc is the document handle of the internal representation of an XML document. -- HTH, Vinod Kumar MCSE, DBA, MCAD, MCSD http://www.extremeexperts.com Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp "Szaki" <ljag@gazeta.pl> wrote in message news:cmsvop$hfo$1@inews.gazeta.pl... > I must import some exemplary file to database (MS Srrver 2000) ofcourse > using procedure Transact SQL. > This file must: > 1.Read the xml file > 2. Create table > 3. Import this date from xml file to my database > > Ps. I create procedure who File xml imports to base, but unfortunately she > only schedule when earlier create a table or table is created.So I need (I > think) create such mini parser in language transact SQL. > Does someone have some ideas? > > For every help Thanks > > ==== example file xml =========================================== > <root> > <Cust> > <IDosoby>1</IDosoby> > <Imie>Lukasz</Imie> > <Nazwisko>Przypadek</Nazwisko> > </Cust> > <Cust> > <IDosoby>2</IDosoby> > <Imie>Dariusz </Imie> > <Nazwisko>Mroz</Nazwisko> > </Cust> > <Cust> > <IDosoby>3</IDosoby> > <Imie>Tomasz</Imie> > <Nazwisko>Kolo</Nazwisko> > </Cust> > </root> > =========================================================== > -- > Luk > > >
vinodk_sctHi
May be it is not exactly what you wanted but I ma sure you will get an idea.
declare @list varchar(8000)
declare @hdoc int
set @list='<Northwind..Orders OrderId="10643"
CustomerId="ALFKI"/><Northwind..Orders OrderId="10692" CustomerId="ALFKI"/>'
select @list='<Root>'+ char(10)+@list
select @List = @List + char(10)+'</Root>'
exec sp_xml_preparedocument @hdoc output, @List
select OrderId,CustomerId
from openxml (@hdoc, '/Root/Northwind..Orders', 1)
with (OrderId int,
CustomerId varchar(10)
)
exec sp_xml_removedocument @hdoc
"Szaki" <ljag@gazeta.pl> wrote in message
news:cmsvop$hfo$1@inews.gazeta.pl...
> I must import some exemplary file to database (MS Srrver 2000) ofcourse
> using procedure Transact SQL.
> This file must:
> 1.Read the xml file
> 2. Create table
> 3. Import this date from xml file to my database
>
> Ps. I create procedure who File xml imports to base, but unfortunately she
> only schedule when earlier create a table or table is created.So I need
(I
> think) create such mini parser in language transact SQL.
> Does someone have some ideas?
>
> For every help Thanks
>
> ==== example file xml ===========================================
> <root>
> <Cust>
> <IDosoby>1</IDosoby>
> <Imie>Lukasz</Imie>
> <Nazwisko>Przypadek</Nazwisko>
> </Cust>
> <Cust>
> <IDosoby>2</IDosoby>
> <Imie>Dariusz </Imie>
> <Nazwisko>Mroz</Nazwisko>
> </Cust>
> <Cust>
> <IDosoby>3</IDosoby>
> <Imie>Tomasz</Imie>
> <Nazwisko>Kolo</Nazwisko>
> </Cust>
> </root>
> ===========================================================
> --
> Luk
>
>
>
uridThanks, Could you correct my procedure that she acted correctly, becouse I'm a beginning programist. thanks for any help U�ytkownik "Vinod Kumar" <vinodk_sct@NO_SPAM_hotmail.com> napisa� w wiadomo�ci news:cmt0tj$1fe$1@news01.intel.com... > Not sure of the exact requirement. But if you can parse the XML and then > using OPENXML shred it into a new table. > > SELECT * INTO <MyNewTable> > FROM OPENXML (@idoc, '/root/') > > Where @idoc is the document handle of the internal representation of an XML > document. > > -- > HTH, > Vinod Kumar > MCSE, DBA, MCAD, MCSD > http://www.extremeexperts.com > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp > > "Szaki" <ljag@gazeta.pl> wrote in message > news:cmsvop$hfo$1@inews.gazeta.pl... > > I must import some exemplary file to database (MS Srrver 2000) ofcourse > > using procedure Transact SQL. > > This file must: > > 1.Read the xml file > > 2. Create table > > 3. Import this date from xml file to my database > > > > Ps. I create procedure who File xml imports to base, but unfortunately she > > only schedule when earlier create a table or table is created.So I need > (I > > think) create such mini parser in language transact SQL. > > Does someone have some ideas? > > > > For every help Thanks > > > > ==== example file xml =========================================== > > <root> > > <Cust> > > <IDosoby>1</IDosoby> > > <Imie>Lukasz</Imie> > > <Nazwisko>Przypadek</Nazwisko> > > </Cust> > > <Cust> > > <IDosoby>2</IDosoby> > > <Imie>Dariusz </Imie> > > <Nazwisko>Mroz</Nazwisko> > > </Cust> > > <Cust> > > <IDosoby>3</IDosoby> > > <Imie>Tomasz</Imie> > > <Nazwisko>Kolo</Nazwisko> > > </Cust> > > </root> > > =========================================================== > > -- > > Luk > > > > > > > >