Something like this:
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
-- this table will contain all items for the batch
CREATE TABLE ##ItemTemp (
UPC varchar(20), SKU varchar(20), Description varchar(100), Department
smallint, SubDepartment smallint, Class smallint,
Unit varchar(6), Price1 money,
Field_Text varchar(30),
Field_1 varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS,
Field_2 varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS,
Field_4 varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS,
NonStock bit, ItemType char(1), CashTrackAcc bit, Serialized bit, TaxNo
int, Loc_Row varchar(4), SellingQty money,
Cost money, CostAve money, Supplier varchar(20), SupplierProdCode
varchar(20), PrintValidate bit,
InActive bit, IsChanged bit )
-- load items from xml
INSERT INTO ##ItemTemp
SELECT * FROM
OPENXML(@docHandle, '/Items/Item', 2)
WITH
(UPC varchar(20), SKU varchar(20), Description varchar(100), Department
smallint, SubDepartment smallint, Class smallint,
Unit varchar(6), Price1 money, Field_Text varchar(30), Field_1
varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS, Field_2 varchar(30), Field_4
varchar(30),
NonStock bit, ItemType char(1), CashTrackAcc bit, Serialized bit, TaxNo
int, Loc_Row varchar(4), SellingQty money,
Cost money, CostAve money, Supplier varchar(20), SupplierProdCode
varchar(20), PrintValidate bit,
InActive bit, IsChanged bit
)
-- dont need xml anymore
EXEC sp_xml_removedocument @docHandle
Edward Koryagin
--- On Wed, 22/4/09, Leigh Wanstead <[email protected]> wrote:
> From: Leigh Wanstead <[email protected]>
> Subject: [DUG] [OFF-TOPIC] MSSQL 2000/2005/2008 Stored procedure to parse xml
> To: "NZ Borland Developers Group - Delphi List" <[email protected]>
> Received: Wednesday, 22 April, 2009, 10:49 AM
> Good morning,
>
> I got data like this in the sql column. How do I parse
> inside sql stored
> procedure to get the value "me"? Any code please.
> I have no trouble to parse
> the value in Delphi 7. But I want to see how can I do it
> inside stored
> procedure?
>
> <hello>
> <hi> me </hi>
> </hello>
>
> TIA
>
> Have a nice day
>
> Regards
> Leigh
>
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: [email protected]
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to [email protected]
> with Subject: unsubscribe
Get the world's best email - http://nz.mail.yahoo.com/
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: [email protected]
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [email protected] with Subject:
unsubscribe
