Hi Team,
I have to exceute the XML as dynamically runtime , but when i execute the below code, the record is not inserted into table.As per my requrement i have to run this code as dynamic query. I share with you all query , please help for this i am very much stuck on this.
Table :
CREATE TABLE EmployeeDetails(
[nvcrMappingCityName] [nvarchar](100) NOT NULL,
[intCityID] [nvarchar](100) NOT NULL,
)
Procedure :
Alter PROCEDURE [dbo].[BulkUpdate]
@XmlDoc xml
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Idoc int;
Declare @Sql nvarchar(4000);
BEGIN TRANSACTION
Begin Try
Set @Sql = '
Exec sp_xml_preparedocument @Idoc output, @XmlDoc;
Insert into EmployeeDetails(nvcrMappingCityName,intCityID)
Select nvcrMappingCityName,intCityID From OpenXML('+@Idoc+',''/NewDataSet/TableName'', 2)
With( nvcrMappingCityName nvarchar(100) ,
intCityID int)';
Exec ( @Sql );
Exec sp_xml_removedocument @Idoc;
Commit Transaction
End Try
Begin Catch
RollBack Transaction
End Catch
END
for the executing above procedure, use below query:
Execute BulkUpdate '<NewDataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="TableName" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="TableName">
<xs:complexType>
<xs:sequence>
<xs:element name="nvcrMappingCityName" type="xs:string" minOccurs="0" />
<xs:element name="intCityID" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<TableName>
<nvcrMappingCityName>Mumbai</nvcrMappingCityName>
<intCityID>1</intCityID>
</TableName>
<TableName>
<nvcrMappingCityName>Nagpur</nvcrMappingCityName>
<intCityID>2</intCityID>
</TableName>
</NewDataSet>'