HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Merge two XML outputs to one from SQL query

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
dbasql-server-2008xmlstackoverflow
mergesqlqueryxmlonetwooutputsfrom

Problem

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.


 
   
    abc
    abc
    abc
   
   Branch
   
    
      a
      abc
    
    
      b
      abc
    
    
      Manual
      abc


And


 
   
     abc.
     abc.
     abc.
   
   Coll
   Leg


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.


  
   
    abc
    abc
    abc
   
   Branch
   
    
      a
      abc
    
    
      b
      abc
    
    
      Manual
      abc
    
   
  
  
   
     abc.
     abc.
     abc.
   
   Coll
   Leg


Please comment below if an

Solution

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:


   
      
         abc
         abc
         abc
      
      Branch
      
         
            a
            abc
         
         
            b
            abc
         
         
            Manual
            abc
         
      
   
   
      
         abc.
         abc.
         abc.
      
      
         Coll
         Lega


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]

Code Snippets

;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')
<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>
;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!ext
<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>
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))

Context

StackExchange Database Administrators Q#191097, answer score: 2

Revisions (0)

No revisions yet.