Merge two XML outputs to one from SQL query

Question

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.

<collection>
 <organisation>
 <orgHeader>
 <orgCode>abc</orgCode>
 <longName>abc</longName>
 <shortName>abc</shortName>
 </orgHeader>
 <orgRole>Branch</orgRole>
 <externalCodes>
 <externalCode>
 <system>a</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>b</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>Manual</system>
 <extCode>abc</extCode>
 </externalCode>
 </externalCodes>
</organisation>

And

<collection>
 <organisation>
 <orgHeader>
 <orgCode>abc.</orgCode>
 <longName>abc.</longName>
 <shortName>abc.</shortName>
 </orgHeader>
 <orgRole>Coll</orgRole>
 <orgRole>Leg</orgRole>
 </organisation>
</collection>

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.

 <collection>
 <organisation>
 <orgHeader>
 <orgCode>abc</orgCode>
 <longName>abc</longName>
 <shortName>abc</shortName>
 </orgHeader>
 <orgRole>Branch</orgRole>
 <externalCodes>
 <externalCode>
 <system>a</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>b</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>Manual</system>
 <extCode>abc</extCode>
 </externalCode>
 </externalCodes>
 </organisation>
 <organisation>
 <orgHeader>
 <orgCode>abc.</orgCode>
 <longName>abc.</longName>
 <shortName>abc.</shortName>
 </orgHeader>
 <orgRole>Coll</orgRole>
 <orgRole>Leg</orgRole>
 </organisation>
</collection>

Please comment below if any other information is required. Thanks.

Answer 1

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:

<collection>
 <organisation>
 <orgHeader>
 <orgCode>abc</orgCode>
 <longName>abc</longName>
 <shortName>abc</shortName>
 </orgHeader>
 <orgRole>Branch</orgRole>
 <externalCodes>
 <externalCode>
 <system>a</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>b</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>Manual</system>
 <extCode>abc</extCode>
 </externalCode>
 </externalCodes>
 </organisation>
 <organisation>
 <orgHeader>
 <orgCode>abc.</orgCode>
 <longName>abc.</longName>
 <shortName>abc.</shortName>
 </orgHeader>
 <orgRole>
 <orgRole>Coll</orgRole>
 <orgRole>Lega</orgRole>
 </orgRole>
 </organisation>
</collection>

---

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:

<collection>
 <organisation>
 <orgHeader>
 <orgCode>abc</orgCode>
 <longName>abc</longName>
 <shortName>abc</shortName>
 </orgHeader>
 <orgRole>Branch</orgRole>
 <externalCodes>
 <externalCode>
 <system>a</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>b</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>Manual</system>
 <extCode>abc</extCode>
 </externalCode>
 </externalCodes>
 </organisation>
 <organisation>
 <orgHeader>
 <orgCode>abc.</orgCode>
 <longName>abc.</longName>
 <shortName>abc.</shortName>
 </orgHeader>
 <orgRole>Coll</orgRole>
 <orgRole>Lega</orgRole>
 <externalCodes>
 <externalCode />
 </externalCodes>
 </organisation>
</collection>

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 <collection> 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'<collection>' AS NVARCHAR(MAX))
+ @nvc_o1
+ @nvc_o2
+ CAST(N'</collection>' AS NVARCHAR(MAX))

output of this:

<collection>
 <organisation>
 <orgHeader>
 <orgCode>abc</orgCode>
 <longName>abc</longName>
 <shortName>abc</shortName>
 </orgHeader>
 <orgRole>Branch</orgRole>
 <externalCodes>
 <externalCode>
 <system>a</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>b</system>
 <extCode>abc</extCode>
 </externalCode>
 <externalCode>
 <system>Manual</system>
 <extCode>abc</extCode>
 </externalCode>
 </externalCodes>
 </organisation>
 <organisation>
 <orgHeader>
 <orgCode>abc.</orgCode>
 <longName>abc.</longName>
 <shortName>abc.</shortName>
 </orgHeader>
 <orgRole>Coll</orgRole>
 <orgRole>Lega</orgRole>
 </organisation>
</collection>

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

• V_immo: Are you sure this answer runs? Because its giving me error at line s(externalCodes) as 's' has more columns than specified in the column list.
• Sabin B: here is the demo of it dbfiddle.uk/…; I don't have your data sources, so I adapted my own. Just a suggestion , it is better , when you post a question , to add also the DDL and DML of your data sources : cpt , pf_map