Using XML from another stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using XML from another stored procedure

Author

Topic

shabazz5th
Starting Member

13 Posts

Posted - 2008-11-10 : 14:20:54

hey guys,

First time poster, hope to be a regular...

I have a question...I am trying to execute a stored procedure by passing it values that a previous stored procedure generate xml for...

so for example:

select
'123' as RouteId,
'ON0123' as UserId,
'1' as HardwareId,
'2008-11-10' as SalesDate,
'1' as RequestType
FOR XML RAW

exec newstored_proc Routeid, UserId, HardwareId, SalesDate, RequestType

Is this at all possible?

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-10 : 14:36:52

yes. you have to pass it in as an XML datatype
so:
declare @myXML xml
select @myXML = (select '123' as RouteId,'ON0123' as UserId,'1' as HardwareId,'2008-11-10' as SalesDate,'1' as RequestType FOR XML RAW)

exec newstored_proc @myXML

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

shabazz5th
Starting Member

13 Posts

Posted - 2008-11-10 : 15:34:44

quote:
Originally posted by spirit1

yes. you have to pass it in as an XML datatype
so:
declare @myXML xml
select @myXML = (select '123' as RouteId,'ON0123' as UserId,'1' as HardwareId,'2008-11-10' as SalesDate,'1' as RequestType FOR XML RAW)

exec newstored_proc @myXML

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Thanks, sorry, I wasn't clear...imagine that the XML RAW statement I did was in another Stored proc...called XMLTest ... so...

exec XMLTest
exec newstored_proc routeid, userid, hardwareid, salesdate, requesttype

thanks again!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-10 : 15:37:21

well you'd have to output the value via the output parameter or do this:

create table #temp(tempXML xml)
insert into #temp
exec XMLTest

declare @myXML xml
select top 1 @myXML = tempXML from #temp

exec newstored_proc @myXML

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

shabazz5th
Starting Member

13 Posts

Posted - 2008-11-11 : 10:39:34

Sorry, I forgot that I am actually connecting to a SQL2000 server, but using SQL2005 Enterprise.

So this didn't work...is there a similar way to do this in SQL2000?

Sorry about posting in the wrong section.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-11 : 10:49:43

well then instead of using an XML datatype use nvarchar.
and use OpenXML:
http://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx

if your XML is larger than 4000 chars you might want to think about ntext datatype.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

shabazz5th
Starting Member

13 Posts

Posted - 2008-11-11 : 11:23:12

Thx Spirit...

So I should be able to do something like this:

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc = (exec XMLTest)

SELECT *
FROM OPENXML (@idoc, '/ROOT/Test',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

When I try to run the first 3 lines I get:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'exec'.

thx again,

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-12 : 00:22:29

As Spirit1 suggested make the stored procedure "XMLTest" return the xml data as output parameter. Read BOL to find out how to use output parameters.

Create procedure XMLTest @idoc OUTPUT
begin
select @idoc = xml data
end

DECLARE @doc nvarchar(1000)
exec XMLTest @doc OUTPUT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 00:32:47

quote:
Originally posted by shabazz5th

Thx Spirit...

So I should be able to do something like this:

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc = (exec XMLTest)

SELECT *
FROM OPENXML (@idoc, '/ROOT/Test',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

When I try to run the first 3 lines I get:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'exec'.

thx again,

you need to first prepare xml document for using OPENXML. have a look at example in books online. It clearly suggests how you prepare xml doc using sp_xml_preparedocument stored proc before parsing and remove it after using sp_xml_removedocument

http://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources