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