For anything beyond a simple

SELECT Col1, Col2 FROM Tab1

I use a stored proc. I want as much SQL logic as is practical to be kept within the database, as this...

Yes, I would expect a single, optimised, trigger to perform better. The best way to confirm this, of course, is to make the changes in a test environment and check...

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

Just noticed your syntax. This should get you closer:

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

EXEC dbo.localprocedure @account, @xml OUT;

SELECT @xml;

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...

Can you take the XSD out of the equation and parse the source XML directly, using T-SQL (after importing the XML into a staging table)?

Or, as suggested above, a Script...

And the problem with your question is that you did not give me the full picture.

Now you are drip-feeding clues into the thread as they come up, which is not...

How about this?

SET @qry
    = 'SET NOCOUNT ON Declare @days INT select @days=LongValue from ' + @db_name
      + '.dbo.Settings

where...

I don't approve of doing this – it wouldn't get past me in a code review – but you can do something like this:

BEGIN TRY
  ...

Sorry for wasting your time all, turns out this package has never been scheduled! There are very similar named packages in the same project, one letter difference! My eyes...

Has the history vanished for all projects, or just this one? Or is it just for this one package?

Rather than ignoring the error, it would be better to avoid it by first checking for the table's existence:

IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
BEGIN
-- Add code here
END;

I'm sure he's on Tik Tok already.

Another version:

CREATE TABLE #X
(
    SomeXML XML
);

INSERT #X
(
    SomeXML
)
VALUES
('<Parameters xmlns="http://www.www.com/WebFramework/WorkflowParameters.xsd">
<Parameter Name="ClientID" StringValue="12903166" />
<Parameter Name="Workflow_ActiveSlide" StringValue="317" />
<Parameter Name="Document_First_UserID" StringValue="8577430" />
<Parameter Name="Program" StringValue="120555" />
<Parameter...

Jeff, to which article are you referring?