Pass XML file in stored procedure as a input parameter from C#

Using xml data type as input parameter of stored procedure, you can store XML documents and fragments in a SQL Server database

Suppose we have XML file as show below:

 
   Inside First Node
 
 
   Inside Second Node 1
   inside Second Node 2
 
 
   Item 1
   Item 2
   Item 3
 

      We can store above XML file using C# code snippet by passing XML file in a stored procedure as shown below

 static void Main(string[] args)
        {
            string SProc = "dbo.usp_XMLParser";
            string ConnectonString = @"Data Source=JITENDRAPAL\SQLEXPRESS;Initial Catalog=JPDB;Integrated Security=True;";
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(@"my.xml");
            using (SqlConnection sqlConnection = new SqlConnection(ConnectonString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(SProc, sqlConnection))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(
                          new SqlParameter("@XMLFile", SqlDbType.Xml)
                          {
                              Value = new SqlXml(new XmlTextReader(xmldoc.InnerXml, XmlNodeType.Document, null))
                          });
                    using (DataTable dataTable = new DataTable())
                    {
                        dataTable.Locale = CultureInfo.InvariantCulture;
                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
                        {
                            sqlDataAdapter.SelectCommand = sqlCommand;
                            sqlDataAdapter.Fill(dataTable);
                        }
                        Console.WriteLine(dataTable.Rows[0]["Col1"].ToString());
                        Console.Read();
                    }
                }
            }

            Console.Read();
        }

In above code:

a.     My.xml is xml file as shown in point 1

b.     We can load xml file using XMLDocument Load method as shown below

 XmlDocument xmldoc = new XmlDocument();
 xmldoc.Load(@"my.xml");

c.     Pass xml file using SQLParameter to the stored procedure as shown below

 sqlCommand.Parameters.Add(
                          new SqlParameter("@XMLFile", SqlDbType.Xml)
                          {
                              Value = new SqlXml(new XmlTextReader(xmldoc.InnerXml, XmlNodeType.Document, null))
                          });

Stored procedure ‘usp_XMLParser’ takes XML file as input and store XML file in SQL table

SQL Script of stored procedure is as shown below:

 USE [JPDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_XMLParser] @XMLFile XML
AS
BEGIN
DECLARE @hdoc [int]
DECLARE @ID [int]
DECLARE @Key [varchar](1000)
DECLARE @NumberOfKeys [int]
DECLARE @Counter [int] = 0;
DECLARE @Value [varchar](2000);

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XMLDataTable]') AND TYPE IN (N'U'))
                   BEGIN
                             DROP TABLE [dbo].[XMLDataTable]
                   END
                   -- Creating table
                   CREATE TABLE [dbo].[XMLDataTable](
            [ID] [int]
                       ,[Key] [varchar](1000)
                             ,[Value] [varchar](2000)
                   )

SET @NumberOfKeys= (SELECT COUNT([Key]) FROM [JPDB].[dbo].[XMLKeysTable]);

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLFile

 WHILE(@Counter < @NumberOfKeys)
 BEGIN
   SELECT @ID= [ID], @Key = [Key] FROM [JPDB].[dbo].[XMLKeysTable] ORDER BY [Key] OFFSET @Counter ROWS FETCH NEXT 1 ROWS ONLY
   SELECT @Value = Value FROM OPENXML (@hdoc, @Key,1) WITH (Value [varchar](1000) 'text()[1]')
   INSERT INTO [dbo].[XMLDataTable]
   VALUES(@ID, @Key, @Value)
   SET @Counter += 1;
  END

--Removes the internal representation of the XML document specified by the document handle and invalidates the document handle
EXEC sp_xml_removedocument @hdoc

SELECT 'true' AS [Col1] 
END

·         Above stored procedure takes XML file as input using XML type input parameter

@XMLFile XML

I am storing XML file in table ‘dbo.XMLDataTable’

To know more about XML parsing in T-SQL and explanation of above stored procedure refer my next post ‘XML Parser in SQL Server’