xml data out of SP

Just like a SP can take XML data as input parameters, is it able to pass out the data as XML to the requesting process?
LVL 5
25112Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

s___kCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
s___k, that explains how it can be done from vb/c#.. how about from within a SP?
0
s___kCommented:
In the document above the example uses "FOR XML" clause, rows returned by the query "Select col1, col2 from sometable FOR XML AUTO" will be in xml.
Read more about FOR XML here: http://msdn.microsoft.com/en-us/library/ms190922.aspx
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ralmadaCommented:
What do you mean by the requesting process??

Is this what you're looing for?

http://www.sommarskog.se/share_data.html#XML
0
25112Author Commented:
OK- i see the syntax..

the requesting process in this case will the SP that will execute the
SELECT... FOR XML statement
and will redirect the output to SSRS for reporting.
0
ralmadaCommented:
>>the requesting process in this case will the SP<<

As shown in the link I've suggested.
0
25112Author Commented:
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?
0
ralmadaCommented:
what the link is saying is that in SQL 2008 you can pass a table as a parameter, which was not possible in 2005. This certainly will not only result in easier coding, since you don't have to extract data from complex XML, but it will also reduce the overhead of having to format it to XML.

0
25112Author Commented:
>>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

0
ralmadaCommented:
1) It could be both
2) I'm not familiar on how Oracle will call an SQL stored procedure. Also I'm not sure why you want to do it.
3) Yes, you just need to parse the XML, as shown in the link,

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)

0
25112Author Commented:
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?
0
ralmadaCommented:
In that case I believe it will be easier to stick to an XML parameter and just pass the XML from Oracle to the SQL Server SP. From there you can "convert" it back to a table as shown in my previous comment, and then pass the table obtained to SSRS.
0
25112Author Commented:
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
0
25112Author Commented:
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?
0
ralmadaCommented:
>>) this is the code you are referring, correct?<<

yes


>>This is different  than table parameter, right?<<

I know, but what I meant to say is that you can parse the XML to a table and then use it in SSRS directly

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

You send a table valued parameter to a stored procedure not to SSRS.

0
25112Author Commented:
helpful guidance..appreciate it..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.