OPENXML for bulk inserts

Hi and thk for your help ;-)
I'm writing a stored procedure for bulk inserts.The sp have 2 parameters:
@.xmlOrders nText,
@.var_id int
I have this xml (@.xmlOrders ) :
<ORDER>
<ORDER>
<art_desc>blablablabla.</art_desc>
<art_code>1</art_code>
<art_units>50</art_units>
<xx>111</xx>
<yy>111</yy>
</ORDER>
<ORDER>
<art_desc>tetetetet.</art_desc>
<art_code>2</art_code>
<art_units>10</art_units>
<xx>222</xx>
<yy>222</yy>
</ORDER>
</ORDER>
I need to insert the parameter @.var_id and this fields from @.xmlOrders:
(art_desc,art_code and art_units) into a table "tbl_orders" with this
structure:
order_id int identity
art_desc varchar
art_code varchar
art_units int
var_id int
How can i modify this for work:
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmlOrders
Insert Into TBL_ORDERS
SELECT art_desc,art_code,art_units
FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
WITH (art_desc varchar(100), art_code varchar(100),art_units int)
XMLOrders
EXEC sp_xml_removedocument @.hDoc
Thank you.
Hello, Oterox!
You wrote on Thu, 28 Oct 2004 15:04:20 +0200:
[Sorry, skipped]
O> FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
The third parameter is the code for default mapping.
1 - attribute centerinc
2 - element centeric
Since you didn't point out the column pattern
O> WITH (art_desc varchar(100), art_code varchar(100),art_units int)
the server use default, etc attribute centerinc mapping. This is not
correct, 'cause you don't have art_desc attribute as well as art_code and
art_units. To make this work you should change the default mapping to
element mapping:
FROM OPENXML (@.hdoc, '/ORDER/ORDER',2) --change the value to 2
or use explicit column mapping
FROM OPENXML (@.hdoc, '/ORDER/ORDER')
WITH(
art_desc varchar(100) 'art_desc',
art_code varchar(100) 'art_code',
art_units int 'art_units'
)
With best regards, Alex Shirshov.
|||If you could send me your procedure and your xml.file.And write me how you
import xml file to sql database.
my e-mail: ljag@.wp.pl
Uytkownik "Oterox" <oterox@.asp404.com> napisa w wiadomoci
news:u0EcT7OvEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hi and thk for your help ;-)
> I'm writing a stored procedure for bulk inserts.The sp have 2 parameters:
> @.xmlOrders nText,
> @.var_id int
> I have this xml (@.xmlOrders ) :
> <ORDER>
> <ORDER>
> <art_desc>blablablabla.</art_desc>
> <art_code>1</art_code>
> <art_units>50</art_units>
> <xx>111</xx>
> <yy>111</yy>
> </ORDER>
> <ORDER>
> <art_desc>tetetetet.</art_desc>
> <art_code>2</art_code>
> <art_units>10</art_units>
> <xx>222</xx>
> <yy>222</yy>
> </ORDER>
> </ORDER>
> I need to insert the parameter @.var_id and this fields from @.xmlOrders:
> (art_desc,art_code and art_units) into a table "tbl_orders" with this
> structure:
> order_id int identity
> art_desc varchar
> art_code varchar
> art_units int
> var_id int
> How can i modify this for work:
> DECLARE @.hDoc int
> exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmlOrders
> Insert Into TBL_ORDERS
> SELECT art_desc,art_code,art_units
> FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
> WITH (art_desc varchar(100), art_code varchar(100),art_units int)
> XMLOrders
> EXEC sp_xml_removedocument @.hDoc
> Thank you.
>
>