September 7, 2010 at 6:08 am
consider i have 2 tables . table1 n table2 with similar columns(empno,ename,salary)
thr is a input xml coming to Stored procedure. i have to process tht xml n based on salary if sal>15000 insert into table1 if sal<15000 insert into table2.
is it possible?.. how can i process each records in a xml in SP?
can anyone help me on this?
September 7, 2010 at 11:51 am
Hi, you have two questions here:
1.) Is it possible to process an XML input to a stored procedure. Answer: Yes, see below.
2.) How do you process each line in the XML. Answer: You can pass the XML into a parameter with the XML datatype. Then you can either use SQL Server's native XQuery to do it (which I recommend) or OPENXML (which is almost always slower and takes up more resources.)
The XQuery path is to essentially create a temp table or table variable and then insert into the temp table or table variable using a the Value and Nodes methods. You can then do whatever you like with the rows in your temp table. See the articles on XQuery in BOL for detailed information, but here's a working example of what I'm talking about:
declare @employeeData xml --this would be your XML input parameter
set @employeeData = '<employeeData>
<employee LastName="Smith" FirstName="Randolph" EmployeeID="1234567"/>
</employeeData>'
declare @xmlTable table (LastName nvarchar(255), FirstName nvarchar(255), EmployeeID int)
insert into @xmlTable (LastName, FirstName, EmployeeID)
select
C.value('@LastName','nvarchar(255)') as LastName,
C.value('@FirstName','nvarchar(255)') as FirstName,
C.value('@EmployeeID','int') as EmployeeID
from
@employeeData.nodes('/employeeData/employee') T(C)
select * from @xmlTable
November 29, 2011 at 3:10 pm
When I try to create a stored procedure reading xml data using your example I get
and error on the select portion
it says "Cannot find either column "C" or the user-defined function or aggregate "C.value", or the name is ambiguous."
This error is on all the select items.
What have I done wrong?
I am using Sql Server 2008.
Dyann
June 11, 2012 at 12:25 am
Kindly try to use as below:
declare @employeeData xml --this would be your XML input parameter
set @employeeData = '<employeeData>
<employee LastName="Smith" FirstName="Randolph" EmployeeID="1234567"/>
</employeeData>'
declare @xmlTable table (LastName nvarchar(255), FirstName nvarchar(255), EmployeeID int)
insert into @xmlTable (LastName, FirstName, EmployeeID)
select
T.C.value('@LastName','nvarchar(255)') as LastName,
T.C.value('@FirstName','nvarchar(255)') as FirstName,
T.C.value('@EmployeeID','int') as EmployeeID
from
@employeeData.nodes('/employeeData/employee') T(C)
select * from @xmlTable
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply