Solved

xml input to a SQL server stored procedure in c#

Posted on 2008-10-01
10
1,799 Views
Last Modified: 2013-12-17
Hi,

I am writting a sql server stored procedure that takes xml as the input parameter. Could you tell me how to do it in C#/stored procedure and how to parse this XML input? Thanks for your help,

0
Comment
Question by:ebi168
  • 6
  • 4
10 Comments
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 350 total points
Comment Utility
Here is an example of parsing the XML input in stored procedure:
http:Q_23683642.html

In the C# code, you would just use the Command.Parameters.Add to add in a parameter of type XML and then pass in your value.  Guess you could just pass in a String parameter and as long as it is valid XML it should be converted to XML in the procedure since that is the input type.  For XML, the type in C# should be SqlTypes.SqlXml.
0
 

Author Comment

by:ebi168
Comment Utility
Thank you mwvisa1. Let me look...
0
 

Author Comment

by:ebi168
Comment Utility
I am completly new to c#, could you help at the line level, should I write something like:

SqlParameter myParameter = new SqlParameter("@myXML", SqlDbType.Text);
myParameter.Direction = ParameterDirection.Input;
myParameter.Value= "'<UA> <i name="UserAgent1" /><i name="UserAgent2" /> <i name="UserAgent3" /></UA> '";
mycommand.Parameters.Add(myParameter);
...
execyte so_executesql @mycommand ...

Please correct me up. Thanks
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 350 total points
Comment Utility
Looks pretty good.  See my suggestions below.

If command type set to stored procedure, think you can just use mycommand.ExecuteNonQuery().
// I would stick with SqlDbType.Xml or SqlDbType.NVarChar here

SqlParameter myParameter = new SqlParameter("@myXML", SqlDbType.Xml);

myParameter.Direction = ParameterDirection.Input;

myParameter.Value = "<UA><i name="UserAgent1" /><i name="UserAgent2" /><i name="UserAgent3" /></UA>"; // Try without single quotes

mycommand.Parameters.Add(myParameter);

Open in new window

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 350 total points
Comment Utility
And here is a much better explaination of how to convert your string to XML datatype in C#.
http://www.codeguru.com/csharp/csharp/cs_data/xml/article.php/c8151
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:ebi168
Comment Utility
thanks let me look and I'll get back with you if got more questions
0
 

Author Comment

by:ebi168
Comment Utility
Hi mwvisa1,

If I call the SP like this:
exec SPName @UserAgentsXML='<UA> <i name=''UserAgent1" /><i name="UserAgent2" /> <i name="UserAgent3" /></UA>'

My first question is: is it 2 single or double quote for the UserAgent1, UserAgent2...etc strig?

Second, here is how my procedure looks like. Could you trouble shooting it as it is not working:
SET @n = 1
SET @params = N'@UAXML XML'

WHILE 1 = 1
 BEGIN
  SET @cmd = N'SET '+ CAST(@UserAgent AS NVARCHAR(256)) +' = @x.value(''(/UA)[' + CAST(@n AS NVARCHAR(3)) + ']'', ''nVARCHAR(256)'')'

  print 'cmd:'+isnull(@cmd,0)
  EXECUTE sp_executesql @cmd, @params, @UAXML = @UserAgentsXML  

  --print 'useragent:'+isnull(@UserAgent,0)
  IF @UserAgent IS NULL
     BREAK

  INSERT INTO table
  SELECT *
  WHERE c.AgentName=@UserAgent
 
  SET @n = @n + 1
END -- while

Thanks very much for your help,
0
 

Author Comment

by:ebi168
Comment Utility
Sorry here is the procedure code:
SET @n = 1
SET @params = N'@UAXML XML'

WHILE 1 = 1
 BEGIN
  SET @cmd = N'SET '+ CAST(@UserAgent AS NVARCHAR(256)) +' = @UAXML.value(''(/UA)[' + CAST(@n AS NVARCHAR(3)) + ']'', ''nVARCHAR(256)'')'

  EXECUTE sp_executesql @cmd, @params, @UAXML = @UserAgentsXML  

  IF @UserAgent IS NULL
     BREAK

  INSERT INTO table
  SELECT *
  WHERE c.AgentName=@UserAgent
 
  SET @n = @n + 1
END -- while

****The @UAXML.value(('/UA)[n]' part, I don't know how to write for the xml input as well. Please suggest. thanks
0
 

Author Comment

by:ebi168
Comment Utility
I just got it. Thanks
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Sorry I missed all this.  Glad you got it.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Object Oriented Best Practice 5 32
Word Template Mail merge with vb.net 4 38
Expando 4 33
c# if statement weird reaction 3 28
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now