How to Pass XML Parameter to Stored Procedure in sql server

In this article, we learn how to pass XML parameter in SQL Server using stored procedure. Many times we want to save multiple values using XML files so this article will help you.


XML file format
<?xml version="1.0" standalone="yes"?>
<Vehicles>
<vehicle>
  <vehicle_id>1</vehicle_id>
    <vehicle_name>Car1</vehicle_name>
    <vehicle_launch_year>2011</vehicle_launch_year>
 </vehicle>
  <vehicle>
  <vehicle_id>2</vehicle_id>
    <vehicle_name>Car2</vehicle_name>
    <vehicle_launch_year>2012</vehicle_launch_year>
 </vehicle>
<vehicle>
  <vehicle_id>3</vehicle_id>

    <vehicle_name>Car3</vehicle_name>
    <vehicle_launch_year>2013</vehicle_launch_year>
 </vehicle>
<vehicle>
  <vehicle_id>4</vehicle_id>
    <vehicle_name>Car4</vehicle_name>
    <vehicle_launch_year>2013</vehicle_launch_year>
 </vehicle>
<vehicle>
  <vehicle_id>5</vehicle_id>
    <vehicle_name>Car5</vehicle_name>
    <vehicle_launch_year>2012</vehicle_launch_year>
 </vehicle>
</Vehicles>
Database:-
Create table:-
create database CodeSolution

USE CodeSolution
GO

/****** Object:  Table [dbo].[tbl_vehicle]    Script Date: 02/26/2017 18:42:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_vehicle](
          [Id] [nvarchar](50) NULL,
          [Name] [nvarchar](50) NULL,
          [LaunchYear] [nvarchar](50) NULL

) ON [PRIMARY]

GO



Now we create stored procedure to save the XML values


CREATE PROCEDURE InsertXMLDate
          -- Add the parameters for the stored procedure here
          @vParam XML
AS
BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO dbo.tbl_vehicle
    SELECT
          vehicle.value('(vehicle_id/text())[1]','nvarchar(50)') AS Id,
          vehicle.value('(vehicle_name/text())[1]','nvarchar(50)') AS Name,
          vehicle.value('(vehicle_launch_year/text())[1]','nvarchar(50)') AS LaunchYear
          FROM
          @vParam.nodes('/Vehicles/vehicle')AS TEMPTABLE(vehicle)
         
END
GO



Now we will execute our stored procedure with xml parameters
USE [CodeSolution]
GO

DECLARE          @return_value int

EXEC        @return_value = [dbo].[InsertXMLDate]
                   @vParam = N'<?xml version="1.0" standalone="yes"?>
<Vehicles>
<vehicle>
  <vehicle_id>1</vehicle_id>
    <vehicle_name>Car1</vehicle_name>
    <vehicle_launch_year>2011</vehicle_launch_year>
 </vehicle>
  <vehicle>
  <vehicle_id>2</vehicle_id>
    <vehicle_name>Car2</vehicle_name>
    <vehicle_launch_year>2012</vehicle_launch_year>
 </vehicle>
<vehicle>
  <vehicle_id>3</vehicle_id>
    <vehicle_name>Car3</vehicle_name>
    <vehicle_launch_year>2013</vehicle_launch_year>
 </vehicle>
<vehicle>
  <vehicle_id>4</vehicle_id>
    <vehicle_name>Car4</vehicle_name>
    <vehicle_launch_year>2013</vehicle_launch_year>
 </vehicle>
<vehicle>
  <vehicle_id>5</vehicle_id>
    <vehicle_name>Car5</vehicle_name>
    <vehicle_launch_year>2012</vehicle_launch_year>
 </vehicle>
</Vehicles>'

SELECT    'Return Value' = @return_value

GO


Out-Put:-
   





You can also learn with below video





How to Pass XML Parameter to Stored Procedure in sql server How to Pass XML Parameter to Stored Procedure in sql server Reviewed by NEERAJ SRIVASTAVA on 9:09:00 PM Rating: 5

No comments:

Powered by Blogger.