Solved

xml input to a SQL server stored procedure in c#

Posted on 2008-10-01
10
1,808 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
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 350 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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 350 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 59

Expert Comment

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

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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