[DUG] [OFF-TOPIC] MSSQL 2000/2005/2008 Stored procedure to parse xml

[Edward Koryagin]

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 <leighw at softtech.co.nz> wrote:

> From: Leigh Wanstead <leighw at softtech.co.nz>
> Subject: [DUG] [OFF-TOPIC] MSSQL 2000/2005/2008 Stored procedure to parse xml
> To: "NZ Borland Developers Group - Delphi List" <delphi at delphi.org.nz>
> 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: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz
> with Subject: unsubscribe


      Get the world&#39;s best email - http://nz.mail.yahoo.com/