SQL: stored procedure with xml data and parsing values

– Stored procedure taking XML as input parameter.

ALTER procedure [dbo].[InsertPcyandCntlInfo] @XMLDOC xml

as

DECLARE @JobID uniqueidentifier

DECLARE @ReportID NVARCHAR(50)

DECLARE @PolicyId uniqueidentifier

DECLARE @PolicyVersion int

Contains logic to parse the xml and read values and assign it to variables.

SELECT @JobID = nref1.value(‘jobid[1]’, ‘uniqueidentifier’) ,@ReportID = nref1.value(‘reportid[1]’, ‘varchar(100)’) ,@PolicyId = nref.value(‘id[1]’,’uniqueidentifier’) , @PolicyVersion = nref.value(‘version[1]’, ‘int’)

FROM (SELECT @XMLDOC As ID)temptable CROSS APPLY ID.nodes(‘//parameter’) AS R(nref),(SELECT @XMLDOC As rootID)temptable1 CROSS APPLY rootID.nodes(‘//root’) AS R1(nref1)

After assign values to the variables, these variable are used to insert values for a table.

INSERT INTO [ReportJob_policyToStatement]

([Jobid]

,[PolicyID]

,[PolicyVersion]

,[StatementID]

,[StatementVersion]

,[Heading])

select @JobID JobID,

PM_Policy.PolicyID,PM_Policy.PolicyVersion,vReport_PolicyControlStatement.StatementID as ControlStatementID,vReport_PolicyControlStatement.StatementVersion, Heading ControlStatementName

FROM

vPolicyCompact AS PM_Policy with(nolock) INNER JOIN vReport_PolicyToStatement AS PM_PolicyStatement with(nolock)

ON PM_Policy.PolicyID = PM_PolicyStatement.PolicyID AND PM_Policy.PolicyVersion = PM_PolicyStatement.PolicyVersion

INNER JOIN vReport_PolicyControlStatement with(nolock) ON vReport_PolicyControlStatement.StatementID = PM_PolicyStatement.StatementID

where PM_Policy.PolicyID =@PolicyID and PM_Policy.PolicyVersion = @PolicyVersion

Execution of Stored proc with the xml as input parameter

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s