Add column name as XML node and join with other table

Question

1. I am trying to query table column names as values of the XML Nodes and in the same node add values from the other table. I have found a code in other post that can create nodes from column names but I'm unable to add nodes from other tables.
2. My second problem is that I want to have node with the original type of the column. But I have not found anything that could help me.

My sample XML should look like this:

<Product>
<ProductName>Product1</ProductName>
<Attributes>
    <Attribute>
        <Name>Attr1</Name>
        <Value>True</Value>
                <Type>BOOL</Type>
    </Attribute>
    <Attribute>
        <Name>Attr2</Name>
            <Value>1.0000000000</Value>
                <Type>DECIMAL</Type>
        </Attribute>
        <Attribute>
        <Name>Weight</Name>
            <Value>155</Value>
                <Type>INT</Type>
        </Attribute>
</Attributes>

</Product>

CREATE TABLE PRODUCT(ProductID int, ProductName nvarchar(40))
INSERT INTO PRODUCT (1, 'Product1')
CREATE TABLE PRODUCT_WEIGHT(ProductID int, CurrentWeight int)
INSERT INTO PRODUCT_WEIGHT(1,155)
CREATE TABLE ATTRIBUTE_(ProductID int, Attr1 BIT, Attr2 decimal(28,10), Attr3 nvarchar(40), Attr4 int)
INSERT INTO ATTRIBUTE_ (1, 1, 1.0, NULL, NULL)

SELECT
ProductName
(
                SELECT(SELECT 
                      C.Name AS [Attribute/Name],
                  C.Value AS [Attribute/Value]
                 FROM  ATTRIBUTE_ A
                 JOIN PRODUCT_WEIGHT pw 
                    ON a.ProductID= pw.ProductID
                 CROSS APPLY (SELECT XMLData = CAST((SELECT a.* FOR XML RAW) AS XML)) B
                 CROSS APPLY (
                                SELECT Name = attr.value('local-name(.)','varchar(100)'),
                                       Value = attr.value('.','varchar(max)')
                                 FROM  B.XMLData.nodes('/row') as A(r)
                                 CROSS APPLY A.r.nodes('./@*') AS B(attr)
                                 WHERE attr.value('local-name(.)','varchar(100)') IN ('Attr1','Attr2', 'Attr3','Weight') 
                             ) C
                WHERE a.ProductID = p.ProductID
                FOR XML PATH(''),TYPE)
                FOR XML PATH ('Attributes'),TYPE
            )

FROM PRODUCT p
FOR XML PATH(''),ROOT('Product')

When I tried:

(SELECT a.*,pw.* FOR XML RAW) AS XML)

It gave me an error: The transaction has aborted. I have also tried:

SELECT
ProductName
(
                SELECT(SELECT 
                      C.Name AS [Attribute/Name],
                  C.Value AS [Attribute/Value]
                 FROM  ATTRIBUTE_ A
                 JOIN PRODUCT_WEIGHT pw 
                    ON a.ProductID= pw.ProductID
                 CROSS APPLY (SELECT XMLData = CAST((SELECT a.* FOR XML RAW) AS XML)) B
                 CROSS APPLY (
                                SELECT Name = attr.value('local-name(.)','varchar(100)'),
                                       Value = attr.value('.','varchar(max)')
                                 FROM  B.XMLData.nodes('/row') as A(r)
                                 CROSS APPLY A.r.nodes('./@*') AS B(attr)
                                 WHERE attr.value('local-name(.)','varchar(100)') IN ('Attr1','Attr2','CurrentWeight') 
                             ) C
                WHERE a.ProductID = p.ProductID
                FOR XML PATH(''),TYPE)
                FOR XML PATH ('Attributes'),TYPE
            ),
(SELECT 'CurrentWeight' AS [Attribute/Name], CurrentWeight AS [Attribute/Value]
FROM PRODUCT_WEIGHT pw2
WHERE pw2.ProductID = p.ProductID
FOR XML PATH('Attibutes'))

FROM PRODUCT p
FOR XML PATH(''),ROOT('Product')

But it just duplicated Attributes node. SQL Version 2019

1. How can I achieve adding nodes from different table?
2. How can I add another node with type of the column?

Answer 1

You are way over-complicating this. Unless you need the ATTRIBUTE_ column names to be dynamic, you can just use CROSS APPLY (VALUES to unpivot them, and cast them all to sql_variant, using SQL_VARIANT_PROPERTY to get the base type.

You can also simplify the subqueries a lot.

SELECT
  ProductName,
  (
    SELECT
        v.Name,
        v.Value,
        SQL_VARIANT_PROPERTY(v.Value, 'BaseType') AS Type
    FROM ATTRIBUTE_ A
    JOIN PRODUCT_WEIGHT pw ON a.ProductID = pw.ProductID
    CROSS APPLY (VALUES
        ('Attr1',         CAST(A.Attr1          AS sql_variant)),
        ('Attr2',         CAST(A.Attr1          AS sql_variant)),
        ('CurrentWeight', CAST(pw.CurrentWeight AS sql_variant))
    ) v(Name, Value)
    WHERE a.ProductID = p.ProductID
      AND v.Value IS NOT NULL
    FOR XML PATH('Attribute'), TYPE, ROOT('Attributes')
  )
FROM PRODUCT p
FOR XML PATH('Product');

db<>fiddle

For bit columns you could special-case it to return BOOL and True/False.