Custom XML Conversion from SQL Query

Question

I am running into an issue where I am having to do some custom encoding on some XML output that results from a SQL Server query.

<?xml version="1.0" encoding="UTF-8"?>
<Services xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="AriesImportSchema.xsd">
   <ServiceLineItem _clientURNExt="ABCD0401712EFG" _siteName="Jerrys Dental Care" _staffLogin="asdasgasdsf" _contractName="17-18 Part A" serviceDate="2018-02-23" program="Ryan Black" primaryService="Oral Dental Care" secondaryService="Routine Treatment" subservice="Surgery Treatment" unitsOfService="1" rateForUnitOfService="18722.12" measurementUnit="Visit" totalCost="18722.12" />
</Services>

The Query to generate the data to be output to the ServiceLineItem tag as records is pretty straightforward:

select [_clientURNExt]
        , [_siteName]
        , [_staffLogin]
        , [_contractName]               
        , [serviceDate]
        , [program]
        , [primaryService]
        , [secondaryService]
        , [subservice]
        , 1 AS [unitsOfService]
        , [rateForUnitOfService] 
        , [measurementUnit]
        , [totalCost]
from ServiceLineItem 

Answer 1

Something like this:

select [_clientURNExt] as [@_clientURNExt]      
from ServiceLineItem 
FOR XML PATH ('ServiceLineItem'),ROOT('Services');