It might be easier to let
SQL Server generate the
XML
, example:
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS
more info here:
Using AUTO Mode[
^]
Example of output to XML file:
Save XML in a file | Read XML from a file (in SQL Server) | SQL with Manoj[
^]