2

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.

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
|improve this answer|||||
  • 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. – V_immo Nov 17 '17 at 15:57
  • 1
    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 – Sabin Bio Nov 17 '17 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.