SQL stored procedure with input of XML string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL stored procedure with input of XML string

Author

Topic

avtuvy
Starting Member

9 Posts

Posted - 2009-03-05 : 09:33:21

I wrote a stored procedure with an XML string as an input, it all works well for the first parameter PLName, each execution inserts a new record into the database with 216600 but it does not populate the PLDescription field, please help


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[InsertPLData_1] (@PLData xml) 
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @PL_FILE TABLE (PLName nvarchar(50),PLDescr nvarchar(100),HWType int,ModemType int,FWVersionLow nvarchar(50),FWVersionHigh nvarchar(50),FWSubLow nvarchar(50),FWSubHigh nvarchar(50),EnableStatus int)
	INSERT INTO @PL_FILE (PLName,PLDEscr) 
	SELECT 
		ParamValues.PLName.value('.','NVARCHAR(50)') ,
		ParamValues.PLDEscr.value('.','NVARCHAR(100)')
	FROM 
		@PLData.nodes('/PLFile/AdminData/FileName'), 
		@PLData.nodes('/PLFile/AdminData/PLDescription') as ParamValues(PLName, PLDEscr)
	
	--select * from PL_FILE 

   SELECT * FROM
	@PL_FILE 
insert into PL_FILE select * from @PL_FILE
	--INNER JOIN
	--@PL_FILE  p
	--ON PL_FILE .PLName = p.PLName




END

Input string


exec InsertPLData_1 @PLData ='<PLFile><AdminData><FileName>216600.txt</FileName><PLDescription>test file</PLDescription><HardwareType>4</HardwareType><FWVersionLow>20</FWVersionLow><FWVersionHigh>27</FWVersionHigh><FWSubLow>1</FWSubLow><FWSubHigh>3</FWSubHigh></AdminData></PLFile>'

avtuvy
Starting Member

9 Posts

Posted - 2009-03-05 : 09:43:00

sometimes I get the error listed below when I parse the proc

Msg 318, Level 15, State 0, Procedure InsertPLData_1, Line 19
The table (and its columns) returned by a table-valued method need to be aliased.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:05:49

try like this


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[InsertPLData_1] (@PLData xml) 
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @PL_FILE TABLE (PLName nvarchar(50),PLDescr nvarchar(100),HWType int,ModemType int,FWVersionLow nvarchar(50),FWVersionHigh nvarchar(50),FWSubLow nvarchar(50),FWSubHigh nvarchar(50),EnableStatus int)
	INSERT INTO @PL_FILE (PLName,PLDEscr) 
	SELECT 
		ParamValues.Items.value('FileName','NVARCHAR(50)') ,
		ParamValues.Items.value('PLDescription','NVARCHAR(100)')
	FROM 
		@PLData.nodes('/PLFile/AdminData') as ParamValues(Items)
	
	--select * from PL_FILE 

   SELECT * FROM
	@PL_FILE 
insert into PL_FILE select * from @PL_FILE
	--INNER JOIN
	--@PL_FILE  p
	--ON PL_FILE .PLName = p.PLName




END

avtuvy
Starting Member

9 Posts

Posted - 2009-03-05 : 13:35:27

Thanks, I tried it and now I am getting
Msg 2389, Level 16, State 1, Procedure InsertPLData_1, Line 16
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Why is that?

avtuvy
Starting Member

9 Posts

Posted - 2009-03-05 : 13:36:52

Wrong line number

Msg 2389, Level 16, State 1, Procedure InsertPLData_1, Line 16
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:47:07

quote:
Originally posted by avtuvy

Wrong line number

Msg 2389, Level 16, State 1, Procedure InsertPLData_1, Line 16
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[InsertPLData_1] (@PLData xml) 
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @PL_FILE TABLE (PLName nvarchar(50),PLDescr nvarchar(100),HWType int,ModemType int,FWVersionLow nvarchar(50),FWVersionHigh nvarchar(50),FWSubLow nvarchar(50),FWSubHigh nvarchar(50),EnableStatus int)
	INSERT INTO @PL_FILE (PLName,PLDEscr) 
	SELECT 
		ParamValues.Items.value('FileName[1]','NVARCHAR(50)') ,
		ParamValues.Items.value('PLDescription[1]','NVARCHAR(100)')
	FROM 
		@PLData.nodes('/PLFile/AdminData') as ParamValues(Items)
	
	--select * from PL_FILE 

   SELECT * FROM
	@PL_FILE 
insert into PL_FILE select * from @PL_FILE
	--INNER JOIN
	--@PL_FILE  p
	--ON PL_FILE .PLName = p.PLName




END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources