Sample to use XML in Stored Procedure

Hi,

Here is a sample to use XML string as a input to a Stored procedure. The stored procedure uses SP sp_xml_preparedocument. Which is capable of reading a XML string. It parses the XML and provides a parsed document which could be used like a temporary table in run time.

Following is the simple code:

1. SQL Script to Create Table:

/****** Object: Table [dbo].[Fund_Index_Relationship] Script Date: 05/24/2009 12:21:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Fund_Index_Relationship](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Fund_ID] [int] NULL,

[FundIndex_ID] [int] NOT NULL,

CONSTRAINT [PK_Fund_Index_Relationship] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

2. Stored Procedure:

-- =============================================

-- Author: Fauzi

-- Create date: 180808

-- Description: TO update Relationship between Fund & Index

-- =============================================

CREATE PROCEDURE [dbo].[XML_SP_Sample]

@xmlString TEXT,

@Fund_ID int,

@Output VARCHAR(1000) OUTPUT

AS

BEGIN

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

-- Insert Section

INSERT into [Fund_Index_Relationship]

(

Fund_ID,

FundIndex_ID

)

SELECT

Fund_ID,

FundIndex_ID

FROM OPENXML (@idoc, '/Items/Item',1)

WITH (

Fund_ID int './Fund_ID',

FundIndex_ID int './FundIndex_ID'

)

END

3. XML string to Test SP:

[XML_SP_Sample] '<Items><Item><Fund_ID>34</Fund_ID><FundIndex_ID>2</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>3</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>4</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>5</FundIndex_ID></Item></Items>'

Hope it Helps :)

Regards

Fauzi