quote:

---

Originally posted by dilipd006

Hi i have a table [ApplicationConfig]



CREATE TABLE [dbo].[ApplicationConfig](
[ConfigID] [smallint] IDENTITY(1,1) NOT NULL,
[ConfigName] [varchar](30) NOT NULL,
[ConfigValue] [varchar](250) NOT NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_ApplicationConfig] PRIMARY KEY CLUSTERED
(
[ConfigID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO






In this ConfigID is PK ConfigName can have multiple ConfigValue but only different values are added

Sample Data
ConfigID [ConfigName] [ConfigValue] [IsActive]
1 Currency INR 1
2 Currency USD 0
3 Currency AD 0

this is a multiple row insert..so insertin with the help of xml
<ApplicationConfig>
<ConfigValue>
<Value>INR</Value>
<IsActive>1</IsActive>
</ConfigValue>
<ConfigValue>
<Value>USD</Value>
<IsActive>0</IsActive>
</ConfigValue>
</ApplicationConfig>


the stored procedure is below






CREATE PROCEDURE [dbo].[uspAppConfigurationInsert]
(
@vchConfigName varchar(30)
,@xmlAppConfig XML
,@tintStatus tinyint OUTPUT
,@vchStatusMessage varchar(50) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
SET @vchStatusMessage = ''
BEGIN

IF EXISTS(-- HERE the validation done to check whether the value exist for this ConfigName or not
SELECT 1
FROM [dbo].[ApplicationConfig]
WHERE ConfigName = @vchConfigName
AND ConfigValue IN (SELECT
V.y.value('./text()[1]','varchar(250)')
FROM @xmlAppConfig.nodes('/ApplicationConfig/ConfigValue/Value') as V(y))

)
BEGIN
SET @tintStatus=1
SET @vchStatusMessage = 'ConfigValue already exist for this ConfigName'
RETURN; -- Returns due to duplication
END
END

INSERT INTO [dbo].[ApplicationConfig]
(
ConfigName
,ConfigValue
,IsActive
)
SELECT
@vchConfigName
,V.y.value('(.//Value)[1]','varchar(250)') AS ConfigValue
,V.y.value('(.//IsActive)[1]','bit') AS IsActive
FROM @xmlAppConfig.nodes('/ApplicationConfig/ConfigValue') as V(y)

SET @tintStatus = 0 --success
RETURN;
END





the procedure insert the value and validate the ConfigValue from the XML tag whether the value exist or not...it will check the whole ConfigValue Elment in the XML and if any ConfigValue is present in the tag it will give an message..

what i want...i want to know which ConfigValue tag is present so that i pass them to the frontend that this tag is already present..Currently it is jst passing the message that ConfigValue is already present not ConfigValue element


Thank you in advance

With regards
DD

---

quote:

---

Originally posted by dilipd006

Here is the data to execute





DECLARE @x xml
SET @x = '<ApplicationConfig>
<ConfigValue>
<Value>INR</Value>
<IsActive>1</IsActive>
</ConfigValue>
<ConfigValue>
<Value>USD</Value>
<IsActive>0</IsActive>
</ConfigValue>
</ApplicationConfig>'
DECLARE @return_value int,
@tintStatus tinyint,
@vchStatusMessage varchar(50)

EXEC @return_value = [dbo].[usp_OTM_WS_AppConfigurationInsert]
@vchConfigName = N'Currency',
@xmlAppConfig = @x,
@tintStatus = @tintStatus OUTPUT,
@vchStatusMessage = @vchStatusMessage OUTPUT

SELECT @tintStatus as N'@tintStatus',
@vchStatusMessage as N'@vchStatusMessage'

SELECT 'Return Value' = @return_value

GO -- this one execute and insert the Config value into the table
--however when i tried to insert the with one new data and one old data
--it doesn't insert even though there is one new data..
--I just want to display the duplicated data that are pesent in the tag.
--below is the new xml after the first xml insertion

<ApplicationConfig>
<ConfigValue>
<Value>AD</Value> --new value to insert
<IsActive>1</IsActive>
</ConfigValue>
<ConfigValue>
<Value>USD</Value> -- duplicate value to display
<IsActive>0</IsActive>
</ConfigValue>
<ConfigValue>
<Value>INR</Value> -- duplicate value to display
<IsActive>1</IsActive>
</ConfigValue>
</ApplicationConfig>

---