如何在存储过程中使用动态查询运行xml [英] how to Run xml using dynamically query in stored procedure

问题描述

Hi Team，



我必须将XML视为动态运行时，但是当我执行下面的代码时，记录不会插入到表中。我的要求我必须将此代码作为动态查询运行。我与大家分享所有疑问，请帮助我，我非常坚持这个。



表：

  CREATE   TABLE  EmployeeDetails（
 [nvcrMappingCityName] [ nvarchar ]（ 100 ） NOT   NULL ，
 [intCityID] [ nvarchar ]（ 100 ） NOT   NULL ，
）

程序：



修改程序[dbo]。[BulkUpdate]

@XmlDoc xml



AS

BEGIN

- 添加SET NOCOUNT ON以防止额外的结果集

- - 干扰SELECT语句。

SET NOCOUNT ON;

声明@Idoc int;

声明@Sql nvarchar（4000）;

BEGIN TRANSACTION

开始尝试





设置@Sql ='

Exec sp_xml_preparedocument @Idoc输出，@ XMLDoc;



插入EmployeeDetails（nvcrMappingCityName，intCityID）



从OpenXML中选择nvcrMappingCityName，intCityID（'+ @ Idoc +'，''/ NewDataSet / TableName''，2）



使用（nvcrMappingCityName nvarchar（100） ），

intCityID int）';





Exec（@Sql）;



执行sp_xml_removedocument @Idoc;

提交交易

结束尝试

开始捕获

RollBack交易

结束捕获

结束



执行上述程序，请使用以下查询：

执行BulkUpdate'<   NewDataSet  >  
 <   xs：schema     id   =  NewDataSet    xmlns   =     xmlns：xs   = < span class =code-keyword> 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  < span class =code-keyword> =  0    maxOccurs   =  unbounded >  
 <   xs：element     name   =  TableName >  
 <   xs：complexType  >  
 <   xs：sequence  >  
 <   xs：element     name   =  nvcrMappingCityName    type   =  xs：string    minOccurs   =  0 < span class =code-attribute>   /  >  
 <   xs：element     name   =  intCityID    type   =  xs：string     minOccurs   =  0    /  >  
 <   / xs：sequence  >  
 <   / xs：complexType  >  
 <   / xs：element  >  
 <   / xs：choice  >  
 <   / xs：complexType  >  
 <   / xs：element  >  
 <   / xs ：架构 >  
 <   TableName  >  
 <   nvcrMappingCityName  > 孟买<   / nvcrMappingCityName  >  
 <   intCityID  >  1 <   / intCityID  >  
 <   / TableName  >  
 <   TableName  >  
 <   nvcrMappingCityName  >  Nagpur <   / nvcrMappingCityName  >  
 <   intCityID  >  2 <   / intCityID  >  
 <   / TableName  >  
 <   / NewDataSet  >  '

解决方案

M使用以下代码存储程序：

 ALTER PROCEDURE [dbo]。[BulkUpdate] 
（
 @XmlDoc xml 
）
 AS 
 BEGIN 
 SET NOCOUNT ON; 
声明@Idoc int 
 BEGIN TRANSACTION 
开始尝试
 Exec sp_xml_preparedocument @Idoc OUTPUT，@ XmlDoc; 
 exec sp_executesql N'
插入EmployeeDetails（nvcrMappingCityName，intCityID）
选择nvcrMappingCityName，intCityID来自OPENXML（@Idoc，''/ NewDataSet / TableName''，2）
 WITH （nvcrMappingCityName varchar（100），
 intCityID int）'，N'@ Idoc int'，@ Idoc 
 Exec sp_xml_removedocument @Idoc; 
提交交易
结束尝试
开始捕获
 RollBack交易
结束捕获
结束
 GO 

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

解决方案

Modify you store procedure with following code..

ALTER PROCEDURE [dbo].[BulkUpdate]
(
	@XmlDoc xml
)
AS
BEGIN
	SET NOCOUNT ON;
	Declare @Idoc int
	BEGIN TRANSACTION
	Begin Try
		Exec sp_xml_preparedocument @Idoc OUTPUT, @XmlDoc;
		exec sp_executesql N'
		Insert into EmployeeDetails(nvcrMappingCityName,intCityID) 
			Select nvcrMappingCityName,intCityID From OPENXML (@Idoc, ''/NewDataSet/TableName'',2)
            WITH (nvcrMappingCityName  varchar(100),
                  intCityID int)', N'@Idoc int',@Idoc
		Exec sp_xml_removedocument @Idoc;
		Commit Transaction
	End Try
	Begin Catch	
		RollBack Transaction
	End Catch
END
GO

这篇关于如何在存储过程中使用动态查询运行xml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！