xml input for stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » xml input for stored procedure

xml input for stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

BeginnerBug

Posted Tuesday, September 07, 2010 6:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 1:16 AM
Points: 127, Visits: 348

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?

Post #981486

charles evans

Posted Tuesday, September 07, 2010 11:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, Visits: 158

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

Post #981785

berglund

Posted Tuesday, November 29, 2011 3:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 5:39 AM
Points: 1, Visits: 1

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

Post #1213592

moh.sharma333

Posted Monday, June 11, 2012 12:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:38 AM
Points: 1, Visits: 4

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

Post #1313672

« Prev Topic | Next Topic »

Permissions