ebi168
asked on
xml input to a SQL server stored procedure in c#
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,
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,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(m
...
execyte so_executesql @mycommand ...
Please correct me up. Thanks
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(m
...
execyte so_executesql @mycommand ...
Please correct me up. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks let me look and I'll get back with you if got more questions
ASKER
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(@UserA
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,
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(@UserA
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,
ASKER
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
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
ASKER
I just got it. Thanks
Sorry I missed all this. Glad you got it.
ASKER