MS SQL XML - Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post

Author

Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Dave Slinn

2005-12-07, 1:24 pm

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...

A: I can create an XML fragment using the For XML Auto clause. No problem
so far.

B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocume
nt and the OPENXML function to create
a table from it - no problem with that.

Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.

Steven Cheng[MSFT]

2005-12-08, 3:24 am

Hi Dave,

Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the

Newsgroups: microsoft.public.sqlserver.programming

One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| From: "Dave Slinn" <CougarDave@noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocume
nt and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
|

Michael Rys [MSFT]

2005-12-14, 8:25 pm

In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...

Best regards
Michael

"Dave Slinn" <CougarDave@noemail.noemail> wrote in message
news:%23Rkm201%23FHA
.3804@TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
>
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
>
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocume
nt and the OPENXML function to
> create
> a table from it - no problem with that.
>
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>
>

Sponsored Links