One final tip: best practice is to qualify all of your object names with their schema names.

Another tip for future posts – for improved readability, please use code blocks when posting code:

CREATE PROCEDURE uspReturnEmployees @departmentName VARCHAR(50) = NULL
AS
SET NOCOUNT ON;

SELECT employeeNo
  ...

Thank you! We do all of our deployments via SSISB catalog (that's why I asked the question). I thought this was now the Microsoft-recommended way of doing things. We...

Thank you for the article and sharing your code and research. While working on it, have you encountered solutions, which do similar things for the packages stored in SSISDB? ...

Brian, thanks for the vote of confidence.

Despite obviously having the desire to suggest that the OP consider purchasing from Nike's latest product line, instead I suggested that adding 'DevOps' to...

While I appreciate your sentiment, and I of course already did that, I thought I'd approach our community to see what others felt was important, not just some regurgitated...

DevOps would be another area I'd suggest.

Try this:

SELECT name = T.c.value('(*:name)[1]', 'varchar (500)')
FROM @xml.nodes('/*:kml/*:Document') T(c);

If I cut and paste from your answer into SSMS, I get syntax errors. I see no DDL or DML....

If you would like a coded solution, please provide sample table structure DDL, sample data in the form of INSERT statements for your sample table, and desired results based on...

Wouldn't you think that as well?

Yes, 100%. Makes your package cleaner too, if you'll pardon the expression. I hate complex SSIS packages (especially those inherited from some other 'one package...

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;