Код

— Server 1

CREATE PROCEDURE api.spXMLParametrExample

 @XML VARCHAR(8000)

AS

BEGIN

 SET NOCOUNT ON;

 DECLARE @xmlTable XML = CAST(@XML AS XML);

 SELECT

  Id = h.l.value('(./@Id)[1]', 'INT'),

  TextValue = h.l.value('(./@TextValue)[1]', 'VARCHAR(50)')

 FROM @xmlTable.nodes('./Head/Line') AS h(l)

END

— Server 2

DECLARE @xml VARCHAR(8000);

SET @xml =

(

 SELECT

  [@Id] = Id,

  [@TextValue] = TextValue

 FROM tblMyTable

 FOR XML PATH('Line'), ROOT('Head')

);

/*

<Head>

<Line Id="1" TextValue="SomeText" />

<Line Id="2" TextValue="SomeText2" />

</Head>

*/

EXEC server1.DB1.api.spXMLParametrExample @XML = @xml;