Solved

xml input to a SQL server stored procedure in c#

Posted on 2008-10-01
10
1,800 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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

25 Experts available now in Live!

Get 1:1 Help Now