Friday, 31 August 2007

Passing XML to a Stored Procedure

declare @sXML xml -- for SQL2000 use varchar(2000)
set @sXML = '

'

DECLARE @hDoc int
DECLARE @zip TABLE (PostalCode varchar(15))
exec sp_xml_preparedocument @hDoc OUTPUT, @sXML
INSERT @zip
SELECT PostalCode
FROM OPENXML(@hDoc, 'root/ZipCode')
WITH (PostalCode VARCHAR(20))
EXEC sp_xml_removedocument @hDoc
SELECT * FROM @Zip

No comments:

Post a Comment