f



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



0
ljag (3)
11/10/2004 11:58:19 AM
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
>
>
>


0
11/10/2004 12:17:54 PM
Hi
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
>
>
>


0
urid (52)
11/10/2004 12:41:24 PM
Thanks,
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
> >
> >
> >
>
>


0
ljag (3)
11/10/2004 12:45:46 PM
Reply: