how to Run xml using dynamically query in stored procedure

See more:

XML

SQL-Server

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>'