What Functions,SP,tools are available within SQL for Creating XML?

Afternoon all,
My ? is as follows. Does anybody have some sample code on How to use MSSQL SP to Create from Query XML formatted data and Save each SP execution to a Specific Folder Location?
MY SP would Be Executed VIA an ADO connection to a DB that would Select * from Production_Declaration where DEC_STATUS > 0 then For Each @i i would need to Fill the XML Elements from My Schema(Of Which i cant figure out how to Load,Use Dynamically as a TABLE) and Save the result to a designated Network(Intra-Net folder) for my ETL Software package to Pull from On Event and Transfer to my Corp SAP System via RFC“Remote Function CALL”