?
Solved

xml input to a SQL server stored procedure in c#

Posted on 2008-10-01
10
Medium Priority
?
1,812 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1400 total points
ID: 22617914
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
ID: 22617945
Thank you mwvisa1. Let me look...
0
 

Author Comment

by:ebi168
ID: 22619154
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
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1400 total points
ID: 22620568
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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1400 total points
ID: 22620576
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
 

Author Comment

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

Author Comment

by:ebi168
ID: 22656112
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
ID: 22656125
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
ID: 22662913
I just got it. Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22664463
Sorry I missed all this.  Glad you got it.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 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