XML Data Insertion in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 342
The below script is having create table and create procedure to insert XML data into a normal SQL table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[COMPLAINT](
[NO] [nvarchar](5) NULL,
[DESCRIPTION] [nvarchar](10) NULL,
[MEANING] [nvarchar](35) NULL,
[ComplaintCode] [nvarchar](38) NULL
) ON [PRIMARY]

GO

CREATE PROCEDURE AddBoardingComplaint
@empXml xml
AS
BEGIN
INSERT INTO [dbo].[COMPLAINT]
(
[NO],
[description],
Meaning
)
SELECT x.value('Id[1]', 'NVARCHAR(5)') AS [NO],
x.value('Description[1]', 'VARCHAR(10)') AS [description],
x.value('Meaning[1]', 'VARCHAR(35)') AS Meaning
FROM @empXml.nodes('//BoardingComplaint') a(x)
END


exec AddBoardingComplaint '<?xml version="1.0"?>
<ArrayOfBoardingComplaint xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<BoardingComplaint>
<Id>5</Id>
<Description>Test Board-5</Description>
<Meaning>Test Board</Meaning>
</BoardingComplaint>
<BoardingComplaint>
<Id>6</Id>
<Description>Test Board-6</Description>
<Meaning>Test Board</Meaning>
</BoardingComplaint>
<BoardingComplaint>
<Id>7</Id>
<Description>Test Board-7</Description>
<Meaning>Test Board</Meaning>
</BoardingComplaint>
</ArrayOfBoardingComplaint>'

SELECT * FROM [dbo].[COMPLAINT]

/* OUTPUT
5 Test Board Test Board NULL
6 Test Board Test Board NULL
7 Test Board Test Board NULL
*/

Comments or Responses

Login to post response