Sql-Server-2008

從 SQL 查詢將兩個 XML 輸出合併為一個

  • November 28, 2017

我有以下程式碼開發 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>

如果需要任何其他資訊，請在下方評論。謝謝。

接近你想要的輸出的東西：

;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當我們在對應列中有空值時，仍然會出現（帶有空值）。

您可以使用兩個變數：一個用於儲存 xml 的第一部分，一個用於儲存第二部分；然後添加前綴<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))

這個的輸出：

<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在這裡

後來添加

使用 bcp 導出： [test].[dbo].[test_xml]這是一個儲存過程，您將在其中放置程式碼。

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

引用自：https://dba.stackexchange.com/questions/191097

相關問答

Sql-Server-2008

儲存 XML 數據的數據類型：VARCHAR(MAX) 或 XML

  • July 31, 2021
檢視問答
Sql-Server-2008

SQL Server 中的 XQuery 將 XML 列數據轉換為關係數據表

  • March 20, 2020
檢視問答
Sql-Server

帶有 XML 參數的 CLR 過程返回根級別的數據無效。第 1 行，位置 1

  • February 10, 2017
檢視問答
Sql-Server-2008

SQL Server - 將表導出到 XML 文件

  • January 3, 2017
檢視問答
Sql-Server-2008

將 NULL xml 值傳遞給某個 CLR 儲存函式會在以某種方式呼叫時崩潰

  • October 10, 2016
檢視問答
Sql-Server

在 MS SQL Server 中修剪 xml 列中的值

  • June 24, 2016
檢視問答