Sql-Server

幫助 SQL 查詢到 XML 輸出

  • March 15, 2020

我有以下查詢：

SELECT    
   [ID] AS [NewID]
   , (
      CASE WHEN LEN([MiddleName]) > 0 THEN
         [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.'
      ELSE
         [LastName] + ', ' + [FirstName]
      END
   ) AS [Name]
   , [Title] AS [ProfTitle]
   , CONVERT(CHAR(10),[DepartureDate],111) AS [BorDepart]
   , [LocationOfficeNumber] AS [OfficePhone]
   , [Location]
   , [LocationSection] AS [Sec]
   , [PracticeGroup] AS [PracticeTL]
   , [Email]         
FROM [dbo].[Borrowers]

有了這個，我想知道如何編寫一個 FOR XML 查詢來轉換為這個輸出：

<sydney>
   <import>
       <template id="Borrower" name="Borrowers">
           <record searchfield="NewID" searchvalue="NewID">
               <field id="NewID">
                   <![CDATA[NEWID]]>
               </field>                
               <field id="Name">
                   <![CDATA[NAME]]>
               </field>                
               <field id="ProfTitle">
                   <![CDATA[Professional Title]]>
               </field>                
               <field id="BorDepart">
                   <![CDATA[YYYY/MM/DD]]>
               </field>                
               <field id="OfficePhone">
                   <![CDATA[office phone]]>
               </field>
               <link id="Location" linkfield="Code">
                   <![CDATA[011]]>
               </link>
               <link id="Sec" linkfield="Term">
                   <![CDATA[SECTION]]>
               </link>
               <link id="PracticeTL" linkfield="Term">
                   <![CDATA[SUBSECTION]]>
               </link>
               <field id="Email">
                   <![CDATA[email]]>
               </field>
           </record>
       </template>
   </import>
</sydney>

我已經嘗試了 FOR XML PATH 和 FOR XML EXPLICIT 的各種組合，但找不到任何答案。我最接近的是這個：

SELECT (
   SELECT 
      'Borrower' AS '@id'
      , 'Borrowers' AS '@name'
      , (
         SELECT
            'NewID' AS '@searchfield'
            , 'NewID' AS '@searchvalue'
            , [ID] AS 'NewID' --AS [Field!1!CDATA]

            , (
               CASE WHEN LEN([MiddleName]) > 0 THEN
                   [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.'
               ELSE
                   [LastName] + ', ' + [FirstName]
               END
            ) AS [Name]
            , [Title] AS [ProfTitle]
            , CONVERT(CHAR(10),[DepartureDate],111) AS [BorDepart]
            , [LocationOfficeNumber] AS [OfficePhone]
            , [Location]
            , [LocationSection] AS [Sec]
            , [PracticeGroup] AS [PracticeTL]
            , [Email]        
         FROM [dbo].[Borrowers]
         FOR XML PATH('record'), TYPE
      )
   FOR XML PATH('template'), TYPE
) AS 'import'
FOR XML PATH('sydney'), TYPE

請提供任何幫助，因為我已經堅持了一周並且找不到任何東西。

PS：這是 Borrowers 表的架構：

在此處輸入圖像描述

乾杯，

阿爾瓦羅·科斯塔

這是一個工作FOR XML EXPLICIT範例。它們有點難以編碼，但我傾向於逐節建構它們，這樣它們就不會那麼糟糕了：

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers
CREATE TABLE dbo.Borrowers
(
   ID                      INT IDENTITY PRIMARY KEY,
   FirstName               VARCHAR(30),
   MiddleName              VARCHAR(30),
   LastName                VARCHAR(30),
   Title                   VARCHAR(30),
   DepartureDate           DATE,
   LocationOfficeNumber    VARCHAR(30),
   Location                VARCHAR(30),
   LocationSection         VARCHAR(30),
   PracticeGroup           VARCHAR(30),
   Email                   VARCHAR(30)
)
GO

INSERT INTO dbo.Borrowers( FirstName, MiddleName, LastName, Title, DepartureDate, LocationOfficeNumber, Location, LocationSection, PracticeGroup, Email )
VALUES 
   ( 'Alvaro', 'X', 'Costa', 'Mr', GETDATE(), '000-000', 'Location 1', 'Location Section 2', 'Practice Group 3', 'alvaro@nospam.com' ),
   ( 'w', '', 'Bob', 'Mr', GETDATE(), '000-007', 'Location 2', 'Location Section 3', 'Practice Group 4', 'wBob@nospam.com' )
GO


SELECT
     1             AS Tag
   , NULL          AS Parent
   , NULL          AS [import!1]
   , NULL          AS [template!2!id]
   , NULL          AS [template!2!name]
   , NULL          AS [record!3!searchfield]
   , NULL          AS [record!3!searchvalue]
   , NULL          AS [record!3!sort!HIDE]

   , NULL          AS [field!4!id]         -- NewID
   , NULL          AS [field!4!!CDATA]

   , NULL          AS [field!4!id]         -- Name
   , NULL          AS [field!4!!CDATA]

   , NULL          AS [field!4!id]         -- ProfTitle
   , NULL          AS [field!4!!CDATA]

   , NULL          AS [field!4!id]         -- BorDepart
   , NULL          AS [field!4!!CDATA]

   , NULL          AS [field!4!id]         -- OfficePhone
   , NULL          AS [field!4!!CDATA]

   , NULL          AS [link!5!id]          -- Location
   , NULL          AS [link!5!linkfield]
   , NULL          AS [link!5!!CDATA]

   , NULL          AS [link!5!id]          -- Sec
   , NULL          AS [link!5!linkfield]
   , NULL          AS [link!5!!CDATA]

   , NULL          AS [link!5!id]          -- PracticeTL
   , NULL          AS [link!5!linkfield]
   , NULL          AS [link!5!!CDATA]

   , NULL          AS [field!4!id]         -- Email
   , NULL          AS [field!4!!CDATA]

UNION ALL

SELECT
     2
   , 1
   , NULL
   , 'Borrower'
   , 'Borrowers'
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

UNION ALL

SELECT
     3
   , 2
   , NULL
   , NULL
   , NULL
   , 'NewID'
   , 'NewID'
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     4
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , 'NewID'
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     4
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , NULL
   , NULL
   , 'Name'
   , LastName
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     4
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , 'ProfTitle'
   , Title
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     4
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , 'BorDepart'
   , CONVERT( CHAR(10), DepartureDate, 111 )
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     4
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , 'OfficePhone'
   , LocationOfficeNumber
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     5
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , 'Location'
   , 'Code'
   , Location
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     5
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , 'Sec'
   , 'Term'
   , LocationSection
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     5
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , 'PracticeTL'
   , 'Term'
   , PracticeGroup
   , NULL
   , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
     4
   , 3
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , ID
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , NULL
   , 'Email'
   , Email

FROM dbo.Borrowers
ORDER BY [record!3!sort!HIDE]
FOR XML EXPLICIT, ROOT('sydney')

高溫高壓

如果你這樣做'NewID' as [field/@id]，你會得到一個field帶有id屬性的元素。在下一行中，您將B.ID as [field]的值ID作為節點值添加到field您在之前的行中創建的節點。

<field id="NewID">1</field>

之後，您需要一個新field節點並創建它，您可以使用沒有名稱且沒有值的節點null as [*]作為節點之間的分隔符field。

這是用於您要創建的每個欄位節點的模式。

'NewID' as [field/@id],
B.ID as [field],
null as [*],

您的查詢將如下所示。

select 'Borrower' as [template/@id],
      'Borrowers' as [template/@name],
      (
      select 'NewID' as [@searchfield],
             'NewID' as [@searchvalue],

             'NewID' as [field/@id],
             B.ID as [field],
             null as [*],

             'Name' as [field/@id],
             case when len(B.MiddleName) > 0
               then B.LastName + ', ' + B.FirstName + ' ' + left(B.MiddleName, 1) + '.'
               else B.LastName + ', ' + B.FirstName
             end as [field],
             null as [*],

             'ProfTitle' as [field/@id],
             B.Title as [field]

     from dbo.Borrowers as B
     for xml path('record'), type
     ) as 'template'
for xml path('import'), root('sydney')

結果

<sydney>
 <import>
   <template id="Borrower" name="Borrowers">
     <record searchfield="NewID" searchvalue="NewID">
       <field id="NewID">1</field>
       <field id="Name">Last, First M.</field>
       <field id="ProfTitle">Tit</field>
     </record>
   </template>
 </import>
</sydney>

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

相關問答

Sql-Server

查看 XML 元素是否存在於文件中具有特定值的任何級別

  • March 18, 2022
檢視問答
Sql-Server

如何使用 SQL Server 中 XML 數據中的序號位置（不是名稱）解析列名？

  • April 26, 2021
檢視問答
Sql-Server

為什麼 SQL 使用相同的 XML 查詢結構進行索引掃描和查找？

  • March 31, 2021
檢視問答
Sql-Server

如何在轉換為 XML 之前在 TSQL 中轉義 XML 字元？

  • April 21, 2020
檢視問答
Sql-Server

T-SQL LIKE 謂詞無法與 XML 轉換的 varchar 中的空格匹配

  • March 17, 2019
檢視問答
Sql-Server

更新格式錯誤的 XML 以允許解析

  • December 17, 2018
檢視問答