Reading XML Doc in SQL Server 2000

XML Doc to Process



Products Table Structure:

ProductID
CategoryID
Description

Reading XML doc inside sp


CREATE PROCEDURE spAddCatalogItems (
@xmlDocument varchar (8000))
AS
BEGIN
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
INSERT INTO Products
SELECT * FROM OPENXML (@docHandle, ‘/ROOT/Category/Product’, 1)
WITH (ProductID int ‘./@ProductID’,
CategoryID int ‘../@CategoryID’,
[Description] varchar (100) ‘./@Description’)
EXEC sp_xml_removedocument @docHandle
END

Objects Used

sp_xml_preparedocument
OPENXML
sp_xml_removedocument

This code is very handy, When you are getting more then one records to process from the UI.
This can be used as standard to get the xml from DAL & process at SQL end ....(By adding exception handling & return output variable.)
If you wanna go crazy then you can further return xml as output parameter too. Sounds cool :)

- Vee