Wednesday, July 3, 2013

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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<root>
 <firstnode>
   <insidefirstnode>Inside First Node</insidefirstnode>
 </firstnode>
 <secondnode>
   <insidesecondnode1>Inside Second Node 1</insidesecondnode1>
   <insidesecondnode2>inside Second Node 2</insidesecondnode2>
 </secondnode>
 <thirdnode>
   <item id="0">Item 1</item>
   <item id="1">Item 2</item>
   <item id="2">Item 3</item>
 </thirdnode>
</root>
      We can store above XML file using C# code snippet by passing XML file in a stored procedure as shown below
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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
?
1
2
XmlDocument xmldoc = new XmlDocument();
xmldoc.Load(@"my.xml");
c.     Pass xml file using SQLParameter to the stored procedure as shown below
?
1
2
3
4
5
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
?
1
@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’

Posted by at
Labels: , , , , ,

3 comments:

Subscribe to: Post Comments (Atom)