save xml inside storeprocedure using openxml

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)

save xml inside storeprocedure using openxml

Author

Topic

shafiqkr
Starting Member

7 Posts

Posted - 2011-12-15 : 08:27:32

i want to save xml data into a table in sp,passed as string,here is my code
alter PROCEDURE usp_be_insertXML
-- Add the parameters for the stored procedure here
@xml varchar(1000)

AS
BEGIN
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET NOCOUNT ON;

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

BEGIN TRY
INSERT INTO testing ([Name] ,[Fname] )
SELECT Column1,Column2
FROM OPENXML (@idoc, 'NewDataSet/Sheet1', 1)
WITH ([Column1] [nvarchar](50),
[Column2] [nvarchar](50))

END TRY
BEGIN CATCH

END CATCH
EXECUTE sp_xml_removedocument @idoc
END
This inserts NULL only in table,any help? and here is XML
<NewDataSet>
<Sheet1>
<Column1>Name</Column1>
<Column2>Fname</Column2>
</Sheet1>
<Sheet1>
<Column1>khan</Column1>
<Column2>dd</Column2>
</Sheet1>
<Sheet1>
<Column1>mytest</Column1>
<Column2>ff</Column2>
</Sheet1>
</NewDataSet>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:07:03

make the proc like below


alter PROCEDURE usp_be_insertXML 
-- Add the parameters for the stored procedure here
@xml xml
AS

INSERT INTO testing ([Name] ,[Fname] )
select t.u.value('(./Column1)[1]','varchar(100)') ,
t.u.value('(./Column2)[1]','varchar(100)') 
from @xml.nodes('/NewDataSet/Sheet1[position()>1]')t(u)
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources