Solved

xml input to a SQL server stored procedure in c#

Posted on 2008-10-01
10
1,805 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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