主页 / dba / 问题 / 191097
Accepted
V_immo
Asked: 2017-11-17 12:36:16 +0800 CST

从 SQL 查询将两个 XML 输出合并为一个

  • 772

我有以下代码开发 2 个 XML 文件，但我希望它们位于一个具有多个组织标签的文件中。

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')

当我运行它时，它会生成 2 个 XML 文件作为输出，如下所示。

<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>

和

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

虽然我想将它添加到单个文件下以将结果作为 Collection 作为 root 并将这些文件合并到组织标记中，如下所示。

 <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>

如果需要任何其他信息，请在下方评论。谢谢。

sql-server-2008 xml

1 个回答

  1. Best Answer

    接近你想要的输出的东西：

    ;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>

    您说orgRole应该删除父标签。我们接近最终解决方案，但还没有。我有一个想法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

    它的输出：

    <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>

    一个小评论：externalCodes/externalCode当我们在对应列中有空值时，仍然会出现（带有空值）。

    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
    • 1

相关问题