XML查询错误，请帮帮我，谢谢 [英] XML query error please help me thank u

查看：68
本文介绍了XML查询错误，请帮帮我，谢谢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

ALTER PROCEDURE [dbo].[BULKINSERTSUDENTDETAIL]
(
	@XMLDATA			XML,
	@LOGINTABLE			VARCHAR(25),
	@OUT				VARCHAR(1000) OUTPUT
)
AS
BEGIN
	SET @OUT = ' ';
	DECLARE @XRECCOUNT		INT;
	DECLARE @COUNT INT;
	CREATE TABLE XMLDATA_TABLE	 
	(
		ROLL_NO				INT,
		STUDENTNAME			VARCHAR(25),
		SYSTEM_NO			INT,
		PASSWORD			VARCHAR(20),
		EMAIL_ID			VARCHAR(40),
		BATCH				VARCHAR(5)
	); 
	DECLARE @SQL			NVARCHAR(4000);
	DECLARE @XROLL_NO		INT;
	DECLARE @XSTUDENTNAME	VARCHAR(25);
	DECLARE @XSYSTEM_NO		INT;
	DECLARE @XPASSWORD		VARCHAR(20);
	DECLARE @XEMAIL_ID		VARCHAR(40);
	DECLARE @XBATCH			VARCHAR(5);

	INSERT INTO XMLDATA_TABLE(ROLL_NO, STUDENTNAME, SYSTEM_NO, PASSWORD,EMAIL_ID, BATCH) 
	SELECT ParamValues.ID.query('ROLL_NO').value('.','INT') ROLL_NO,
			ParamValues.ID.query('STUDENTNAME').value('.','NVARCHAR(25)') STUDENTNAME,
			ParamValues.ID.query('SYSTEM_NO').value('.','INT') SYSTEM_NO,
			ParamValues.ID.query('PASSWORD').value('.','NVARCHAR(20)') PASSWORD,
			ParamValues.ID.query('EMAIL_ID').value('.','NVARCHAR(40)') EMAIL_ID,
			ParamValues.ID.query('BATCH').value('.','NVARCHAR(5)') BATCH
	FROM @XMLDATA.nodes('/DATA/ID') AS ParamValues(ID);
	SET @COUNT  = (SELECT COUNT (*) FROM XMLDATA_TABLE);	
	IF @COUNT  = 0
	BEGIN
		SET @OUT ='AN ERROR OCCURED'; 
		DROP TABLE XMLDATA_TABLE
		RETURN;
	END;
	ELSE
	BEGIN
		BEGIN  TRANSACTION
		SET @SQL =('DELETE FROM '+ @LOGINTABLE + '
					INSERT INTO '+ @LOGINTABLE +'(ROLL_NO,STUDENTNAME,SYSTEM_NO,PASSWORD,EMAIL_ID,BATCH)
					 SELECT ROLL_NO,STUDENTNAME,SYSTEM_NO,PASSWORD,EMAIL_ID,BATCH FROM XMLDATA_TABLE');
			EXEC SP_EXECUTESQL @SQL,N'@LOGINTABLE VARCHAR(30)',@LOGINTABLE;		
			DROP TABLE XMLDATA_TABLE;
			SET @OUT = 'SUCCESS';
		COMMIT;
		
	END;	
END;




通过这种方式，我成功插入了一行，但是插入了多行却引发错误....

添加了代码块[/编辑]




by this i insert one row successfully but more than one row insertion raise error....

Code block added[/Edit]

推荐答案

更改存储过程，如下所示
xml格式，如
Change your stored procedure like below
xml formate like 
<college>
 <student_details>
  <roll_no>10000</roll_no>
  <studentname>XYZ</studentname>
  <system_no>11</system_no>
  <password>123</password>
  <email_id>123@GMAIL.COM
  <batch>123</batch>
 </email_id></student_details>
 <student_details>
  <roll_no>10001</roll_no>
  <studentname>XYZ1</studentname>
  <system_no>12</system_no>
  <password>1231</password>
  <email_id>1231@GMAIL.COM
  <batch>1231</batch>
 </email_id></student_details>
</college> 





ALTER PROCEDURE [dbo].[BULKINSERTSUDENTDETAIL]
(
	@XMLSTRING			VARCHAR(MAX),
	@LOGINTABLE			VARCHAR(25),
	@OUT				VARCHAR(1000) OUTPUT
)

AS
BEGIN
	SET @OUT = ' ';
	DECLARE @XRECCOUNT		INT;
	DECLARE @COUNT INT;
	CREATE TABLE XMLDATA_TABLE	 
	(
		ROLL_NO				INT,
		STUDENTNAME			VARCHAR(25),
		SYSTEM_NO			INT,
		PASSWORD			VARCHAR(20),
		EMAIL_ID			VARCHAR(40),
		BATCH				VARCHAR(5)
	); 
	DECLARE @SQL			NVARCHAR(4000);
	DECLARE @XROLL_NO		INT;
	DECLARE @XSTUDENTNAME	VARCHAR(25);
	DECLARE @XSYSTEM_NO		INT;
	DECLARE @XPASSWORD		VARCHAR(20);
	DECLARE @XEMAIL_ID		VARCHAR(40);
	DECLARE @XBATCH			VARCHAR(5);
	DECLARE @XMLDATA                XML;
        SELECT @XMLDATA =@XMLSTRING
        INSERT INTO XMLDATA_TABLE(ROLL_NO, STUDENTNAME, SYSTEM_NO,PASSWORD,EMAIL_ID, BATCH)
       SELECT	convert(INT,STUDENT_DETAIL.ITEM.query('./ROLL_NO').value('.','INT')),
 convert(VARCHAR(25),STUDENT_DETAIL.ITEM.query('./STUDENTNAME').value('.','VARCHAR(25)')),
convert(INT,STUDENT_DETAIL.ITEM.query('./SYSTEM_NO').value('.','INT')),
convert(VARCHAR(20),STUDENT_DETAIL.ITEM.query('./PASSWORD').value('.','VARCHAR(20)')),
convert(VARCHAR(40),STUDENT_DETAIL.ITEM.query('./EMAIL_ID').value('.','VARCHAR(40)')),
convert(VARCHAR(5),STUDENT_DETAIL.ITEM.query('./BATCH').value('.','VARCHAR(5)'))

	FROM		@XML.nodes('/COLLEGE/STUDENT_DETAILS') AS STUDENT_DETAIL(ITEM)

	SET @COUNT  = (SELECT COUNT (*) FROM XMLDATA_TABLE);	
	IF @COUNT  = 0
	BEGIN
		SET @OUT ='AN ERROR OCCURED'; 
		DROP TABLE XMLDATA_TABLE
		RETURN;
	END;
	ELSE
	BEGIN
		BEGIN  TRANSACTION
		SET @SQL =('DELETE FROM '+ @LOGINTABLE + '
					INSERT INTO '+ @LOGINTABLE +'(ROLL_NO,STUDENTNAME,SYSTEM_NO,PASSWORD,EMAIL_ID,BATCH)
					 SELECT ROLL_NO,STUDENTNAME,SYSTEM_NO,PASSWORD,EMAIL_ID,BATCH FROM XMLDATA_TABLE');
			EXEC SP_EXECUTESQL @SQL,N'@LOGINTABLE VARCHAR(30)',@LOGINTABLE;		
			DROP TABLE XMLDATA_TABLE;
			SET @OUT = 'SUCCESS';
		COMMIT;
		
	END;	
END;


ALTER PROCEDURE [dbo].[BULKINSERTSUDENTDETAIL]
(
	@XMLDATA			XML,
	@LOGINTABLE			VARCHAR(25)='LOGINTABLE',
	@OUT				VARCHAR(1000) OUTPUT
)
AS
BEGIN
	SET @OUT = ' ';
	DECLARE @XRECCOUNT		INT;
	DECLARE @COUNT INT;
	CREATE TABLE XMLDATA_TABLE	 
	(
		ROLL_NO				INT,
		STUDENTNAME			VARCHAR(25),
		SYSTEM_NO			INT,
		PASSWORD			VARCHAR(20),
		EMAIL_ID			VARCHAR(40),
		BATCH				VARCHAR(5)
	); 
	
	DECLARE @SQL			NVARCHAR(4000);
	DECLARE @XROLL_NO		INT;
	DECLARE @XSTUDENTNAME	VARCHAR(25);
	DECLARE @XSYSTEM_NO		INT;
	DECLARE @XPASSWORD		VARCHAR(20);
	DECLARE @XEMAIL_ID		VARCHAR(40);
	DECLARE @XBATCH			VARCHAR(5);
 print 'hi;'
 
	INSERT INTO XMLDATA_TABLE(ROLL_NO, STUDENTNAME, SYSTEM_NO, PASSWORD,EMAIL_ID, BATCH) 
	SELECT ParamValues.ID.query('ROLL_NO').value('.','INT') ROLL_NO,
			ParamValues.ID.query('STUDENTNAME').value('.','NVARCHAR(25)') STUDENTNAME,
			ParamValues.ID.query('SYSTEM_NO').value('.','INT') SYSTEM_NO,
			ParamValues.ID.query('PASSWORD').value('.','NVARCHAR(20)') PASSWORD,
			ParamValues.ID.query('EMAIL_ID').value('.','NVARCHAR(40)') EMAIL_ID,
			ParamValues.ID.query('BATCH').value('.','NVARCHAR(5)') BATCH
	FROM @XMLDATA.nodes('/DATA/ID') AS ParamValues(ID);
	select * from XMLDATA_TABLE
	SET @COUNT  = (SELECT COUNT (*) FROM XMLDATA_TABLE);	
	IF @COUNT  = 0
	BEGIN
		SET @OUT ='AN ERROR OCCURED'; 
		DROP TABLE XMLDATA_TABLE
		RETURN;
	END;
	ELSE
	BEGIN
		BEGIN  TRANSACTION
		SET @SQL =('DELETE FROM '+ @LOGINTABLE + '
					INSERT INTO '+ @LOGINTABLE +'(ROLL_NO,STUDENTNAME,SYSTEM_NO,PASSWORD,EMAIL_ID,BATCH)
					 SELECT ROLL_NO,STUDENTNAME,SYSTEM_NO,PASSWORD,EMAIL_ID,BATCH FROM XMLDATA_TABLE');
			EXEC SP_EXECUTESQL @SQL,N'@LOGINTABLE VARCHAR(30)',@LOGINTABLE;		
			DROP TABLE XMLDATA_TABLE;
			SET @OUT = 'SUCCESS';
		COMMIT;
		
	END;	
END;





EXEC BULKINSERTSUDENTDETAIL '<DATA><ID>
  <ROLL_NO>1</ROLL_NO>
  <STUDENTNAME>BHAVESH</STUDENTNAME>
  <SYSTEM_NO>1</SYSTEM_NO>
  <PASSWORD>BK</PASSWORD>
  <EMAIL_ID>BKEMAIL</EMAIL_ID>
  <BATCH>2</BATCH></ID>
  <ID>
  <ROLL_NO>2</ROLL_NO>
  <STUDENTNAME>BHAVESH</STUDENTNAME>
  <SYSTEM_NO>1</SYSTEM_NO>
  <PASSWORD>BK</PASSWORD>
  <EMAIL_ID>BKEMAIL</EMAIL_ID>
  <BATCH>2</BATCH></ID>
</DATA>','LOGINTABLE','1'


这篇关于XML查询错误，请帮帮我，谢谢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆