C# send xml to sql

Question

Hello,

Can someone provide an example of how to send an xml from C# to SQL Server, and then loop on the xml in the stored procedure that got it, and update or enter line by line.

TY

Answer 1

I am making a lot of assumptions as your question is not clear.

What you could do is create a stored procedure in MSSQL that accept a string as parameter. This string should contain the Xml that you want to process. See the following snippet for a possible stored procedure that processes the XML.

CREATE PROCEDURE [dbo].[sproc_ProcessXml] ( @data AS NTEXT ) AS /* Create a temporary table to insert the Xml data*/
IF (OBJECT_ID(N'tempdb..#temp') IS NOT NULL ) DROP TABLE #temp

CREATE TABLE [dbo].[#temp] 
( 
  /* your table definition */
)

DECLARE @handle AS INTEGER
DECLARE @flags  AS INTEGER 

EXEC sp_xml_preparedocument @handle OUTPUT, @data

/* Supported is only element mapping */ SET @flags = 2

INSERT INTO [dbo].[#temp] 
  SELECT [Insert xml fields] 
  FROM OPENXML (@handle, '[Xpath in XmlDocument', @flags)
  WITH (Id  INTEGER)

EXEC sp_xml_removedocument @handle  

/* Select data from Xml and from several normal tables if needed */ SELECT [Insert your Fields] FROM dbo.[#temp] t

DROP TABLE [dbo].[#temp]

RETURN

Answer 2

Check out the three-part series on SQL XML on 15seconds: http://www.15seconds.com/Issue/050803.htm.

I would personally use the SQL XQuery functions to shred apart your XML into bits and pieces and store those in SQL Server.

If you have something like:

<data>
  <person>
     <name>Jones</name>         
     <firstname>Peter</firstname>
  </person>
  <person>
     <name>Smith</name>         
     <firstname>Frank</firstname>
  </person>
<data>

you can write something like:

SELECT
   Data.Person.value('(name)[1]', 'varchar(20)') as 'Name',
   Data.Person.value('(firstname)[1]', 'varchar(20)') as 'First Name'
FROM 
   @XmlVar.nodes('/data/person') As Data(Person)

So basically, the .nodes function shreds your XML into a "pseudo-table" Data.Person - each <person> entry becomes one row in the table.

With the .value() function, you can extract single values from those shredded XML nodes. You now have a bunch of varchar(20) fields, that can be e.g. inserted into a table.

This method works well if your XML is fairly small (a few hundred entries). If you have huge XML files, you might want to investigate other methods, such as XML Bulkload.