HI,
Please help me to resolve this issue.. Im trying to fetch records from xml to database...

The XML is like
isrMasterData>
- <safeGuardRatingList>
- <safeGuardRating>
<code>HS</code>
<name>Highly Satisfactory</name>
</safeGuardRating>
</safeGuardRatingList>

Now i have a table in db(with 2 columns as shortname and name ) where i have a short name to "safeGuardRating" as SGR.. I need to retrieve that from table using the join of safeGuardRating..

here im using Case statement .. But im unsuccesful.. Please help me out.. Im new to SQL Server..



USE [Activities_DEV]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [Activities].[FORMXML_Insert]
(
@Filename NVARCHAR(256)
)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON

CREATE TABLE #tblISRFORM
(
Projxml XML
)

--variables to xml and dynamic sql
DECLARE @SQLStatement NVARCHAR(1000)
,@xmlProj XML



BEGIN TRY

--Insert row into @tblMaster table
-- with xml column


SET @SQLStatement = N'Insert into #tblISRFORM(Projxml)
SELECT *
FROM
(
SELECT * FROM OPENROWSET(
BULK ''' + @Filename + ''', SINGLE_BLOB
)AS P ) M'

--PRINT @SQLStatement

EXECUTE sp_executesql @SQLStatement

SELECT @xmlProj = Projxml
FROM #tblISRFORM

--End of @tblISRFORM entries

--Start of load into Activities.ISRFORMCODE table

BEGIN TRANSACTION

PRINT 'ISRFORMCODE Started'

Insert into Activities.ISRFormCode(ISRFormTypeCode,ISRFormType Name, ISRFormType )

SELECT p.x.value(N'(./code)[1]','nvarchar(10)') ISRFormTypeCode
,p.x.value(N'(./name)[1]','nvarchar(256)') ISRFormTypeName
, Case '/isrMasterData/' When p.x.value(N'/isrMasterData/safeGuardRatingList/safeGuardRating')Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'safeGuardRating')
When p.x.value(N'/isrMasterData/orafRatingTypes/orating') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'orating')
When p.x.value(N'/isrMasterData/safeGuardsTemplate/safeGuards') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'safeGuards')
When p.x.value(N'/isrMasterData/responsibilityTypes/responsibility') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'responsibility')
When p.x.value(N'/isrMasterData/actionStatusTypes/actionStatus') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'actionStatus')
When p.x.value(N'/isrMasterData/actionStatusTypes/actionStatus') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'timingStageTypes')
When p.x.value(N'/isrMasterData/timingStageTypes/timingStage') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'timingStage')
When p.x.value(N'/isrMasterData/tranchCondDelayedStatusList/tranchCondDelayedStatus') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'tranchCondDelayedStatus')
when p.x.value(N'/isrMasterData/agencyTypes/agencyType') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'agencyType')
When p.x.value(N'/isrMasterData/tranchStatusList/tranchStatus') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'tranchStatus')
When p.x.value(N'/isrMasterData/tranchCondPendingReasonList/tranchCondPendingReason') Then
(select fcd.IsrFormCode from Activities.ISRFormCodeDetail fcd
where fcd.ISRFormCodeName = 'tranchCondPendingReason')
END
FROM @xmlProj.nodes('/isrMasterData/') P(x) AS temp


COMMIT TRANSACTION

--End of load into Activities.ISRFORMCODE tables


RETURN 0

END TRY

BEGIN CATCH

DECLARE @ERROR_MESSAGE NVARCHAR(256)

SELECT @ERROR_MESSAGE = ERROR_MESSAGE()

IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END

--RAISERROR(@ERROR_MESSAGE,16,1)
RAISERROR('MasterXML log error ',16,1)

RETURN -1

END CATCH

END

GO