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>CurrentWeight</Name>
            <Value>155</Value>
                <Type>INT</Type>
        </Attribute>
</Attributes>

</Product>

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
            )

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.

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