Just remove the @ from the column aliases to get the result as a node.

As I said in the previous post, switching encoding is not supported so the output will have to be a character string representation of the xml:

😎

DECLARE @XML XML = N'';

DECLARE @XMLSTR NVARCHAR(MAX) = N'';

SELECT @XML = (

SELECT

SDH.SalesOrderID AS 'SalesOrderID'

,(SELECT

SD.OrderQty AS 'Product/OrderQty'

,SD.CarrierTrackingNumber AS 'Product/ProductID/SalesOrderDetailID/CarrierTrackingNumber'

,SD.ProductID AS 'Product/ProductID'

,SD. SalesOrderDetailID AS 'Product/ProductID/SalesOrderDetailID'

FROM Sales.SalesOrderDetail SD

WHERE SD.SalesOrderID = SDH.SalesOrderID

FOR XML PATH(''),TYPE) AS Products

FROM Sales.SalesOrderHeader SDH

WHERE SDH.SalesOrderID = 44116

FOR XML PATH('SalesOrder'),TYPE, ROOT('SalesOrders'));

select @XMLSTR = N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>

<VFPDataSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\PHC15\AddTableToSchema_Nest.xsd">' + CAST(@XML AS NVARCHAR(MAX))

SELECT @XMLSTR;