[CODE]
Create procedure GetXMLAsInput
@xmlString varchar(1000)
as
insert into person values(--what should i do to iterate over the xml and perform multiple insertion)
[/CODE]
/*
Hi,
Below is a very simple scenario where you can parse the xml in sql server (2005 and above) and put data into a table.
You can use the sql server xml data type for the purpose.
*/
Declare @xmlData Xml
Set @xmlData = '<data>
<row Id="1">AAA</row>
<row Id="2">BBB</row>
<row Id="3">CCC</row>
</data>'
-- In the table below we will put data from the xml.
Declare @Data Table (Id Int, Name Varchar(100))
--
Select r.c.value('@Id[1]', 'int'),
r.c.value('.[1]', 'varchar(100)')
From @xmlData.nodes ('data/row') r(c)
-- Note your stored procedure should look like the one below
Create Procedure [Stored Proc Name]
@xmlData Xml
Begin
-- Code
End
-- For more stuff on xml data type please visit http://msdn.microsoft.com/en-us/library/ms190798
spartynaved
Member
3 Points
34 Posts
how to solve this problem ?
May 31, 2012 12:49 PM|LINK
chandrasheka...
Star
10258 Points
1760 Posts
Re: how to solve this problem ?
May 31, 2012 12:58 PM|LINK
Hi
Refer the following link
http://weblogs.asp.net/manojkdotnet/archive/2010/01/26/bulk-insert-using-gridview-and-sql-server-xml.aspx
http://stackoverflow.com/questions/7146265/bulk-insert-using-xml
http://www.dotnetspider.com/resources/31343-Bulk-Insert-using-XML-Data-SQL-Server.aspx
Please try the answer for the post and finally Don't forget to click “Mark as Answer” on the post that helped you.
FarhanK
Contributor
2603 Points
350 Posts
Re: how to solve this problem ?
Jun 01, 2012 05:19 AM|LINK
/* Hi, Below is a very simple scenario where you can parse the xml in sql server (2005 and above) and put data into a table. You can use the sql server xml data type for the purpose. */ Declare @xmlData Xml Set @xmlData = '<data> <row Id="1">AAA</row> <row Id="2">BBB</row> <row Id="3">CCC</row> </data>' -- In the table below we will put data from the xml. Declare @Data Table (Id Int, Name Varchar(100)) -- Select r.c.value('@Id[1]', 'int'), r.c.value('.[1]', 'varchar(100)') From @xmlData.nodes ('data/row') r(c) -- Note your stored procedure should look like the one below Create Procedure [Stored Proc Name] @xmlData Xml Begin -- Code End -- For more stuff on xml data type please visit http://msdn.microsoft.com/en-us/library/ms190798Regards,
Farhan Uddin Khan
Breeze Technologies
sangeetonly
Member
174 Points
43 Posts
Re: how to solve this problem ?
Jun 01, 2012 05:52 AM|LINK
Create PROCEDURE [dbo].[GetXMLAsInput]
@xmlString varchar(max)
AS
DECLARE @idoc int
DECLARE @ERR Int
SET @ERR = 0
BEGIN
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
BEGIN TRY
insert into person (personname,personmobileno,persondob)
SELECT personname,personmobileno,persondob
FROM OPENXML (@idoc, '/NewDataSet/Nodename',2)with
(personname varchar(100),personmobileno int ,persondob datetime)
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity
= ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
EXEC sp_xml_removedocument @idoc
--SELECT @ERR
END
("I would love to change the world, but they won’t give me the source code")
Please: Don't forget to click "Mark as Answer" on the post that helped you. That way future readers will know which post solved your issue.