Running SP Remotely that returns XML Document

Question

Running SP Remotely that returns XML Document

Paul Stasny

Mr or Mrs. 500

Points: 599
More actions
June 18, 2020 at 10:26 pm

#3764267
Greetings,
I am trying to remotely run a SP that accepts a single interger input and outputs an XML Document via a select statement from an @xml variable. My intent is to loop through about 900 different integer inputs for this SP and then shred the xml output to SQL columns. I know I can't run this via a Linked Server due to the XML but based on a previous post, I thought I would be able to create a local SP that called this SP but I am still running into error re the XML.
The remote SP accepts 1 integer input (account) and Select from an @xml variable defined as XML
I created a local SP as follows and am calling it as shown below but can't get the syntax quite right. First, is it possible to call a remote SP that returns XML and if so, is there a better method than what I amn trying to do. If not, any syntax suggestions would be appreciated. At this point, I can't get the remote output into either a local variable or temp table. Thanks
Declare @xml xml
Declare @account int
Set @account = 1
exec dbo.localprocedure
@account
@xml = @xml output
Select * from @xml
@xml = @xml out
Select @xml
```
CREATE PROCEDURE [dbo].[localProcedure]
@account int,
@xml XML Output
AS
Exec [RemoteServer].[RemoteDB].[dbo].[Remote Procedure] @account
Select * from @xml
```

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

I don't know the answer, but if the problem you are having is specifically because of the XML datatype, have you considered casting the XML to NVARCHAR() and then casting it back as necessary?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246970 More actions · Answer 2

Just noticed your syntax. This should get you closer:

DECLARE @xml XML;
DECLARE @account INT = 1;

EXEC dbo.localprocedure @account, @xml OUT;

SELECT @xml;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246970 More actions · Answer 3

Your CREATE PROC needs work too – it needs to assign the variable @XML.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel