For fun, here is a slightly different way of constructing the desired XML output

😎

USE tempdb;

GO

SET NOCOUNT ON;

GO

;WITH XMLNAMESPACES

(

'http://www.example-namespace.com' AS ns

)

,SAMPLE_DATA AS

(

SELECT * FROM

(VALUES ('Example1','Example2')

) AS X(ex1,ex2)

)

SELECT

'Main' AS 'ns:Main'

,SD.ex1 AS 'ns:SubPath/ex1'

,SD.ex2 AS 'ns:SubPath/ex2'

FROM SAMPLE_DATA SD

FOR XML PATH(''), ROOT('ns:doc');

Output

<ns:doc xmlns:ns="http://www.example-namespace.com">

<ns:Main>Main</ns:Main>

<ns:SubPath>

<ex1>Example1</ex1>

<ex2>Example2</ex2>

</ns:SubPath>

</ns:doc>