xml data out of SP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks ralmada

>>
XML to pass data back to the caller, but you could also use XML to pass data in the opposite direction. (Although on SQL 2008, it is certainly cleaner to use table parameters.)
<<

Can you help explain if table parameters is a better option to send data from the xml to SSRS through another connection string?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

>>SQL 2008 you can pass a table as a parameter
1)should this be only a physical table or a temp table or table variable also can be passed on.?
2)Can SQL 2008 receive a table as a parameter? (can oracle send a table that the SQL SP can take as a variable)
3)can you get parameter as XML and then send it back as a table parameter to the next destination

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

thanks for clarrifying, ralmada..

>>I'm not familiar on how Oracle will call an SQL stored procedure. Also I'm not sure why you want to do it.

through a webservice.

oracle db sends parameters through webservice to sql sp..sql sp then executes and send back results as xml or table parameter to ssrs.. does this make sense?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

thanks again..

1)
>>you can "convert" it back to a table as shown in my previous comment, and then pass the table obtained to SSRS

2) this is the code you are referring, correct?

SELECT au_id = T.item.value('@au_id', 'varchar(11)'),
au_lname = T.item.value('@au_lname', 'varchar(40)'),
au_fname = T.item.value('@au_fname', 'varchar(20)')
FROM @x.nodes('/authors') AS T(item)

This is different than table parameter, right?
http://msdn.microsoft.com/en-us/library/bb510489.aspx

ASKER

for #1 i meant to ask this:
1)
>>you can "convert" it back to a table as shown in my previous comment, and then pass the table obtained to SSRS

what are the options available to send Table-Valued Parameters to SSRS, for it to generate the report.. how would you do it?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.