Merge two XML outputs to one from SQL query I have following code developing 2 XML files but I would like them to be in one file itself with multiple Organization tag.

SELECT 
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
(
SELECT system, extCode from
(
    Select 'a' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
    UNION ALL 
    Select 'b' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
    UNION ALL 
    Select 'Manual' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
) a
FOR XML PATH('externalCode'), TYPE, ELEMENTS,Root('externalCodes')
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

SELECT 
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/shortName",
(
SELECT orgRole from
(
    Select 'Coll' AS 'orgRole'
    UNION ALL 
    Select 'Lega' AS 'orgRole'
) a
FOR XML PATH(''), TYPE, ELEMENTS
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')


When I run this It produces 2 XML files as output as below.

&lt;collection&gt;
 &lt;organisation&gt;
   &lt;orgHeader&gt;
    &lt;orgCode&gt;abc&lt;/orgCode&gt;
    &lt;longName&gt;abc&lt;/longName&gt;
    &lt;shortName&gt;abc&lt;/shortName&gt;
   &lt;/orgHeader&gt;
   &lt;orgRole&gt;Branch&lt;/orgRole&gt;
   &lt;externalCodes&gt;
    &lt;externalCode&gt;
      &lt;system&gt;a&lt;/system&gt;
      &lt;extCode&gt;abc&lt;/extCode&gt;
    &lt;/externalCode&gt;
    &lt;externalCode&gt;
      &lt;system&gt;b&lt;/system&gt;
      &lt;extCode&gt;abc&lt;/extCode&gt;
    &lt;/externalCode&gt;
    &lt;externalCode&gt;
      &lt;system&gt;Manual&lt;/system&gt;
      &lt;extCode&gt;abc&lt;/extCode&gt;
    &lt;/externalCode&gt;
  &lt;/externalCodes&gt;
&lt;/organisation&gt;




And

&lt;collection&gt;
 &lt;organisation&gt;
   &lt;orgHeader&gt;
     &lt;orgCode&gt;abc.&lt;/orgCode&gt;
     &lt;longName&gt;abc.&lt;/longName&gt;
     &lt;shortName&gt;abc.&lt;/shortName&gt;
   &lt;/orgHeader&gt;
   &lt;orgRole&gt;Coll&lt;/orgRole&gt;
   &lt;orgRole&gt;Leg&lt;/orgRole&gt;
 &lt;/organisation&gt;
&lt;/collection&gt;


While I want to add it under single file to produce results as Collection as root and merge these files in organization tag as follows.

 &lt;collection&gt;
  &lt;organisation&gt;
   &lt;orgHeader&gt;
    &lt;orgCode&gt;abc&lt;/orgCode&gt;
    &lt;longName&gt;abc&lt;/longName&gt;
    &lt;shortName&gt;abc&lt;/shortName&gt;
   &lt;/orgHeader&gt;
   &lt;orgRole&gt;Branch&lt;/orgRole&gt;
   &lt;externalCodes&gt;
    &lt;externalCode&gt;
      &lt;system&gt;a&lt;/system&gt;
      &lt;extCode&gt;abc&lt;/extCode&gt;
    &lt;/externalCode&gt;
    &lt;externalCode&gt;
      &lt;system&gt;b&lt;/system&gt;
      &lt;extCode&gt;abc&lt;/extCode&gt;
    &lt;/externalCode&gt;
    &lt;externalCode&gt;
      &lt;system&gt;Manual&lt;/system&gt;
      &lt;extCode&gt;abc&lt;/extCode&gt;
    &lt;/externalCode&gt;
   &lt;/externalCodes&gt;
  &lt;/organisation&gt;
  &lt;organisation&gt;
   &lt;orgHeader&gt;
     &lt;orgCode&gt;abc.&lt;/orgCode&gt;
     &lt;longName&gt;abc.&lt;/longName&gt;
     &lt;shortName&gt;abc.&lt;/shortName&gt;
   &lt;/orgHeader&gt;
   &lt;orgRole&gt;Coll&lt;/orgRole&gt;
   &lt;orgRole&gt;Leg&lt;/orgRole&gt;
 &lt;/organisation&gt;
&lt;/collection&gt;


Please comment below if any other information is required. Thanks.
Something close to your desired output:

;with cpt
AS 
(
       Select 'abc' as cptname
)
,sys
AS
(
      Select 'a' as "system"
      union all Select 'b' as "system"
      union all Select 'Manual' as "system"
)
,org
AS
(
      Select 'Coll' AS "orgRole"
      union all Select 'Lega'
)

SELECT 
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
s."externalCodes"
FROM
   cpt as c1
   CROSS APPLY
   (SELECT 
        s."system" as "externalCode/system",
        LTRIM(RTRIM(c1.cptname)) as "externalCode/extCode"
    FROM sys as s
    FOR XML PATH(''), TYPE, ELEMENTS
    )s(externalCodes)

UNION ALL

SELECT
LTRIM(RTRIM(c1.cptname)) +'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) +'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) +'.' as "corgHeader/shortName",
o.orgRole as "orgRole",
null 
FROM
   cpt as c1
   CROSS APPLY
   (SELECT org.orgRole 
     FROM org
     FOR XML PATH(''), TYPE, ELEMENTS
   ) as o(orgRole)

FOR XML PATH('organisation'), root ('collection')


output for it:

&lt;collection&gt;
   &lt;organisation&gt;
      &lt;orgHeader&gt;
         &lt;orgCode&gt;abc&lt;/orgCode&gt;
         &lt;longName&gt;abc&lt;/longName&gt;
         &lt;shortName&gt;abc&lt;/shortName&gt;
      &lt;/orgHeader&gt;
      &lt;orgRole&gt;Branch&lt;/orgRole&gt;
      &lt;externalCodes&gt;
         &lt;externalCode&gt;
            &lt;system&gt;a&lt;/system&gt;
            &lt;extCode&gt;abc&lt;/extCode&gt;
         &lt;/externalCode&gt;
         &lt;externalCode&gt;
            &lt;system&gt;b&lt;/system&gt;
            &lt;extCode&gt;abc&lt;/extCode&gt;
         &lt;/externalCode&gt;
         &lt;externalCode&gt;
            &lt;system&gt;Manual&lt;/system&gt;
            &lt;extCode&gt;abc&lt;/extCode&gt;
         &lt;/externalCode&gt;
      &lt;/externalCodes&gt;
   &lt;/organisation&gt;
   &lt;organisation&gt;
      &lt;orgHeader&gt;
         &lt;orgCode&gt;abc.&lt;/orgCode&gt;
         &lt;longName&gt;abc.&lt;/longName&gt;
         &lt;shortName&gt;abc.&lt;/shortName&gt;
      &lt;/orgHeader&gt;
      &lt;orgRole&gt;
         &lt;orgRole&gt;Coll&lt;/orgRole&gt;
         &lt;orgRole&gt;Lega&lt;/orgRole&gt;
      &lt;/orgRole&gt;
   &lt;/organisation&gt;
&lt;/collection&gt;




You said that the parent tag orgRole should be removed. We are close to the final solution , but not yet there.
I have an idea with FOR XML EXPLICIT

;with cpt
AS 
(
       Select 'abc' as cptname
)
,sys
AS
(
      Select 'a' as "system"
      union all Select 'b' as "system"
      union all Select 'Manual' as "system"
)
,org
AS
(
      Select 'Coll' AS "orgRole"
      union all Select 'Lega'
)
,cte_source
AS
(
SELECT LTRIM(RTRIM(c1.cptname)) as cptname,
'Branch' as orgRole,
s.system,
s.extcode
FROM
   cpt as c1
   CROSS APPLY
   (SELECT 
        s.system ,
        LTRIM(RTRIM(c1.cptname)) as extCode
    FROM sys as s
    )s(system,extCode)

UNION ALL

SELECT
LTRIM(RTRIM(c1.cptname)) +'.' ,
o.orgRole,
null,
null
FROM
   cpt as c1
   CROSS APPLY
   (SELECT org.orgRole 
     FROM org
   ) as o(orgRole)
)

--select * from cte_source

SELECT 1 as Tag
    ,NULL as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,NULL as [orgHeader!3!orgCode!ELEMENT]
    ,NULL as [orgHeader!3!longName!ELEMENT]
    ,NULL as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]

UNION ALL

-- for organisation
SELECT DISTINCT
    2 as Tag
    ,1 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!] 
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for orgHeader
SELECT DISTINCT
    3 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c


UNION ALL

--for orgRole
SELECT DISTINCT
    4 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,c.orgRole as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c


UNION ALL

--for externalCodes
SELECT DISTINCT
    5 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname  as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c


UNION ALL

--for externalCode
SELECT DISTINCT
    6 as Tag
    ,5 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,c.system as [externalCode!6!system!ELEMENT]
    ,c.extCode as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c


ORDER BY 
    [organisation!2!]
    ,[orgHeader!3!orgCode!ELEMENT]
    --,[orgRole!4!]
    ,[externalCode!6!system!ELEMENT]
    ,[externalCode!6!extCode!ELEMENT]

FOR XML EXPLICIT


the output for it:

&lt;collection&gt;
  &lt;organisation&gt;
    &lt;orgHeader&gt;
      &lt;orgCode&gt;abc&lt;/orgCode&gt;
      &lt;longName&gt;abc&lt;/longName&gt;
      &lt;shortName&gt;abc&lt;/shortName&gt;
    &lt;/orgHeader&gt;
    &lt;orgRole&gt;Branch&lt;/orgRole&gt;
    &lt;externalCodes&gt;
      &lt;externalCode&gt;
        &lt;system&gt;a&lt;/system&gt;
        &lt;extCode&gt;abc&lt;/extCode&gt;
      &lt;/externalCode&gt;
      &lt;externalCode&gt;
        &lt;system&gt;b&lt;/system&gt;
        &lt;extCode&gt;abc&lt;/extCode&gt;
      &lt;/externalCode&gt;
      &lt;externalCode&gt;
        &lt;system&gt;Manual&lt;/system&gt;
        &lt;extCode&gt;abc&lt;/extCode&gt;
      &lt;/externalCode&gt;
    &lt;/externalCodes&gt;
  &lt;/organisation&gt;
  &lt;organisation&gt;
    &lt;orgHeader&gt;
      &lt;orgCode&gt;abc.&lt;/orgCode&gt;
      &lt;longName&gt;abc.&lt;/longName&gt;
      &lt;shortName&gt;abc.&lt;/shortName&gt;
    &lt;/orgHeader&gt;
    &lt;orgRole&gt;Coll&lt;/orgRole&gt;
    &lt;orgRole&gt;Lega&lt;/orgRole&gt;
    &lt;externalCodes&gt;
      &lt;externalCode /&gt;
    &lt;/externalCodes&gt;
  &lt;/organisation&gt;
&lt;/collection&gt;


a small remark : externalCodes/externalCode still appears (with null) when we have null values in the corespondent columns.  

You can use two variables : one to store the first part of the xml and one to store the second part; then add as prefix &lt;collection&gt; and a suffix `'

declare @cpt table
( 
    cptname varchar(10)
)
insert into @cpt(cptname)
values('abc')

declare @sys table
(
    system varchar(10)
)
insert into @sys(system)
values('a'),('b'),('Manual')


declare @org table
(
    orgRole varchar(10)
)
insert into @org(orgRole)
values('Coll'),('Lega')

declare @nvc_o1 nvarchar(max)=N''
     ,@nvc_o2 nvarchar(max)=N''


SET @nvc_o1 = (

SELECT 
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
(
SELECT system, extCode from
(
    Select 'a' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
    UNION ALL 
    Select 'b' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
    UNION ALL 
    Select 'Manual' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
) a
FOR XML PATH('externalCode'), TYPE, ELEMENTS,Root('externalCodes')
)
from  @cpt as c1
FOR XML PATH(''), root ('organisation')
)

SET @nvc_o2 = (
SELECT 
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/shortName",
(
SELECT orgRole from @org
FOR XML PATH(''), TYPE, ELEMENTS
)
from  @cpt as c1
FOR XML PATH(''), root ('organisation')
)

select CAST(N'&lt;collection&gt;' AS NVARCHAR(MAX))
+ @nvc_o1
+ @nvc_o2
+ CAST(N'&lt;/collection&gt;' AS NVARCHAR(MAX))


output of this:

&lt;collection&gt;
   &lt;organisation&gt;
      &lt;orgHeader&gt;
         &lt;orgCode&gt;abc&lt;/orgCode&gt;
         &lt;longName&gt;abc&lt;/longName&gt;
         &lt;shortName&gt;abc&lt;/shortName&gt;
      &lt;/orgHeader&gt;
      &lt;orgRole&gt;Branch&lt;/orgRole&gt;
      &lt;externalCodes&gt;
         &lt;externalCode&gt;
            &lt;system&gt;a&lt;/system&gt;
            &lt;extCode&gt;abc&lt;/extCode&gt;
         &lt;/externalCode&gt;
         &lt;externalCode&gt;
            &lt;system&gt;b&lt;/system&gt;
            &lt;extCode&gt;abc&lt;/extCode&gt;
         &lt;/externalCode&gt;
         &lt;externalCode&gt;
            &lt;system&gt;Manual&lt;/system&gt;
            &lt;extCode&gt;abc&lt;/extCode&gt;
         &lt;/externalCode&gt;
      &lt;/externalCodes&gt;
   &lt;/organisation&gt;
   &lt;organisation&gt;
      &lt;orgHeader&gt;
         &lt;orgCode&gt;abc.&lt;/orgCode&gt;
         &lt;longName&gt;abc.&lt;/longName&gt;
         &lt;shortName&gt;abc.&lt;/shortName&gt;
      &lt;/orgHeader&gt;
      &lt;orgRole&gt;Coll&lt;/orgRole&gt;
      &lt;orgRole&gt;Lega&lt;/orgRole&gt;
   &lt;/organisation&gt;
&lt;/collection&gt;


dbfiddle here 

LATER ADDED

to export with bcp:
[test].[dbo].[test_xml] it is a stored procedure where you will place your code inside.

declare @sql nvarchar(4000) 
select @sql  = N'bcp.exe "EXEC [test].[dbo].[test_xml]" queryout "d:\csv\comm.txt" -c -t, -T -S'+ @@servername 
exec master..xp_cmdshell @sql