I am not aware of any method similar to SELECT * FROM xml.

I use the text() for performance.  If you look at the execution plans for the the same query...

Use something like this, and change your data types as needed

SELECT 
    id          = XT.XC.value('(id/text())[1]',...

This should get you what you are looking for

SELECT 
    XT.XC.value('(id/text())[1]', 'int')
FROM
    @MyXMLString.nodes('/items/item') AS XT(XC)

In order to compare NULL values, you need to convert them to a known value.

Below is how I would do it, with 3 different data types

DECLARE @Var1...

...

He hasn't quite figured out how to tell us when he needs to go potty, so all we can do is get him seated on it until he goes....

Thanks Rod.

At the risk of turning this...

We had a cruise booked.  Then came a hard lock-down, so our cruise was cancelled.

We are confined to our homes - Only allowed to go out to purchase groceries or...

CREATE TABLE [dbo].[A]([DatabaseName] [nvarchar](1000) NULL, [DBSize] [nvarchar](1000) NULL) 
insert into A values (DatabaseA , 580960.00 MB)
insert into A values(DatabaseB, 23523.00 GB)
insert into A values(DatabaseC,234.00 KB)

Table values:

DatabaseA   580960.00...

You are going to have to check the joins.  Without knowing your tables and data, I have attempted to change the joins, so that I can add the OUTER APPLY...

Please see the code below.

Which tables contain the fields countryCode and site_id ?

  FROM    dbo.
        ,...

Help us out by providing the source table name for each field in the WHERE clause

So, what is the name of the view?

Do you really have a table or view name dbo?

The structure of yor SQL does not make sense to me.  Is this for SQL Server, or some other flavour of SQL?

You could keep the function, but change it to an inline TVF, like this

CREATE FUNCTION dbo.Stringmultitypes ( @id int )
RETURNS table
AS
  RETURN SELECT Types = CAST(STUFF((
...

If the source and destination tables are the same structure, you can swap out the partitions