STRING_AGG() Error. Conversion failed when converting the varchar value to data type int

I needed to convert a table insert/update trigger to a Service Broker task.

The INSERTED table is used but is passed from the Trigger as XML variable to the SP and executed asynchronously under the Broker Service. Again this is not the issue as I have the Trigger/SP/Broker working for mundane task like logging data to a log table.

Below is a block of code that was successfully executing within a Stored procedure and logging data to a log table.

INSERT INTO [dbo].[MapLocationLog](
    [lo_location]
	, [lo_Location_Code]
)
SELECT
    inserted.[lo_location]
	, inserted.lo_location_Code 
FROM (
	SELECT
		X.query('.').value('(row/lo_Location)[1]', 'int') AS lo_location
		, X.query('.').value('(row/lo_Location_Code)[1]', 'nvarchar(100)') AS lo_location_Code
	FROM @inserted.nodes('inserted/row') AS T(X)
) AS inserted 

This is a modification that uses STRING_AGG() to create a delimited array of integers. Also logging successfully to a table. Note the change in type in the X.query for "lo_Location" changing from INT to NVARCHAR(10).

INSERT INTO [dbo].[MapLocationLog]([lo_Location_Code])
(
    SELECT String_agg(inserted.lo_location, ',')
    FROM (
        SELECT
		    X.query('.').value('(row/lo_Location)[1]', 'nvarchar(10)') AS lo_location
		FROM @inserted.nodes('inserted/row') AS T(X)
	) AS inserted
)

What I'm trying to do is get a comma delimited list of IDs and use these for subsequent query filtering.

This is where things go wrong.

DECLARE @ids NVARCHAR(max) 
SET @ids = (
    SELECT STRING_AGG(inserted.lo_location), ',')
	FROM (
        SELECT
			X.query('.').value('(row/lo_Location)[1]', 'nvarchar(10)') AS lo_location
		FROM @inserted.nodes('inserted/row') AS T(X)
	) AS inserted
)

This is the error

> Conversion failed when converting the nvarchar value '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20' to data type int.

The error line is on this
SET @ids = (SELECT STRING_AGG(inserted.lo_location), ',')

I have been over this so many times and I cannot find where I'm going wrong. I've tried

• CASTING
  • CAST(inserted.lo_location, NVARCHAR(10))
  • The whole Select statement

The only thing I can centre on is the STRING_AGG is explicitly NVARCHAR/VARCHAR and the Xpath value has implicit typing suggesting "1" is implicitly type as INT. I'm no expert on Xpath but that's my reading.
What is confusing is where the code thinks I'm trying to convert to INT as there is no INT declarations.

Grateful for any insight.

base on your example I created an xml and simplified your query
if you can provide an original XML - that would be helpful

DECLARE @xml XML = '<inserted><row><lo_Location>123</lo_Location></row>'+
					'<row><lo_Location>222</lo_Location></row>'+
					'<row><lo_Location>123456789123456</lo_Location></row>'+
					'<row><lo_Location></lo_Location></row>'+
					'<row><lo_Location>aaa</lo_Location></row>'+
					'<row><lo_Location>aaafgnhjfgy</lo_Location></row>'+
					'<row></row>'+
					'</inserted>'
DECLARE @out VARCHAR(100)

SELECT @out = STRING_AGG(i.lo_location, ',')
   FROM (
        SELECT
            X.query('.').value('(row/lo_Location)[1]', 'nvarchar(10)') AS lo_location
        FROM @xml.nodes('inserted/row') AS T(X)
   ) i

SELECT @out

result: 123,222,1234567891,,aaa,aaafgnhjfg

NOTE: in your first query you are using location as integer, in other queries you have nvarchar(10), which should "eat everything"...
i would assume in table MapLocationLog column lo_Location_Code is NOT integer.
In addition, TRY TO AVOID USING RESERVED WORDS. such as inserted/deleted, etc..