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
