• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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?
0
25112
Asked:
25112
  • 8
  • 6
  • 2
8 Solutions
 
s___kCommented:
0
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now