Insert multiple records from a XML string differing on one parameter in SQL SERVER 2008
- by Rohit
Below in a query which inserts records to SimpleDictationProfileMapping table after reading it from a XML string. Now this query inserts a single record in which DictationCaptureProfileID is @dictationCaptureProfileId . Now i want to insert multiple rows in which @dictationCaptureProfileId is different and other 2 values are same.
What i want to achieve by this is in case parent changes all child values should also change.
INSERT INTO SimpleDictationProfileMapping
(
DictationCaptureProfileID,
DictationProfileMappingAttributeID,
DictationProfileMappingAttributeValue
)
SELECT @dictationCaptureProfileId,
row.value('@attrId','varchar(max)'),
row.value('@value', 'varchar(max)')
FROM @simpleDictationCaptureProfileMappings.nodes('/simpleMappingAtribute/attribute')
AS d ( row ) ;
I want
INSERT INTO SimpleDictationProfileMapping
(
DictationCaptureProfileID OR (SELECT DictationCaptureProfileID
FROM DictationCaptureProfile
WHERE SystemDictationCaptureProfileID = @systemDictationCaptureProfileID),
DictationProfileMappingAttributeID,
DictationProfileMappingAttributeValue
)
SELECT @dictationCaptureProfileId ,
row.value('@attrId','varchar(max)'),
row.value('@value', 'varchar(max)')
FROM @simpleDictationCaptureProfileMappings.nodes ('/simpleMappingAtribute/attribute')
AS d ( row ) ;
Please tell how to achieve this.
