对于具有两个父节点和多个子节点的XML结构？

Question

我需要的XML进行结构是这样的：

<Loans> 
    <Loan> 
     <GUID></GUID> 
     <LoanFolder></LoanFolder> 
     <LastModified></LastModified> 
     <TotalMonthlyPayment></TotalMonthlyPayment> 
     <AgentCompanyName></AgentCompanyName> 
    <LoanSecondaryStatusDates> 
     <LoanSecondaryStatus> 
      <StatusName>Name</StatusName> 
      <StatusDate>Date</StatusDate> 
     </LoanSecondaryStatus> 
     <LoanSecondaryStatus> 
     <StatusName>Name</StatusName> 
     <StatusDate>Date</StatusDate> 
     </LoanSecondaryStatus> 
    </LoanSecondaryStatusDates> 
</Loan> 
</Loans> 

我有一个FOR XML查询的结构是这样的：

SELECT 
    [GUID] 
    ,[LOANFOLDER] 
    ,[LASTMODIFIED] 
    ,[LASTIMPORTED] 
    ,[TotalMonthlyPayment] 
    ,[AgentCompanyName], 
    (
    SELECT 
    'Borrower Docs Sent/Req' as 'StatusName', 
    CASE WHEN t.BorrowerDocsSent IS NOT NULL THEN t.BorrowerDocsSent ELSE '' END as 'StatusDate' 

FROM Encompass_loanData as t 
WHERE t.[GUID] = E.[GUID] 
FOR XML PATH('LoanSecondaryStatus'), TYPE 
), 
    (
    SELECT 
    t.BorrowerCity as 'StatusName', 
    t.[GUID] as 'StatusDate' 

FROM Encompass_loanData as t 
WHERE t.[GUID] = E.[GUID] 
FOR XML PATH('LoanSecondaryStatus'), TYPE 
) 

From Encompass_loanData E 
WHERE [LASTMODIFIED] >= '20160121' 
FOR XML PATH ('Loan'), type, root('Loans') 

这让我接近，但缺少顶“ LoanSecondaryStatusDates“根节点。我如何添加这个根节点？ http://sqlfiddle.com/#!6/d672a/2/0

注：我能使用这个XSL来添加节点：

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
    <xsl:output indent="yes"/> 
    <xsl:strip-space elements="*"/> 

    <xsl:template match="@*|node()"> 
     <xsl:copy> 
      <xsl:apply-templates select="@*|node()"/> 
     </xsl:copy> 
    </xsl:template> 

    <xsl:template match="Loan"> 
     <xsl:copy> 
      <xsl:apply-templates select="@*|node()[not(self::LoanSecondaryStatus)]"/> 
      <LoanSecondaryStatusDates> 
       <xsl:apply-templates select="LoanSecondaryStatus"/> 
      </LoanSecondaryStatusDates> 
     </xsl:copy> 
    </xsl:template> 

</xsl:stylesheet> 

Answer 1

这是一个可能的SQL查询，根据您的SQL小提琴表结构建成，将生产所需的XML格式：

SELECT 
     [GUID] 
     ,[LOANFOLDER] 
     ,[LASTMODIFIED] 
     ,[LASTIMPORTED] 
     ,[TotalMonthlyPayment] 
     ,[AgentCompanyName] 
     ,(
     SELECT * FROM 
     (
      SELECT 
       CONVERT(VARCHAR(100), t.TotalMonthlyPayment) as 'StatusName', 
       t.[GUID] as 'StatusDate' 

      FROM [Sample] as t 
      WHERE t.[GUID] = E.[GUID] 
      UNION 
      SELECT 
       'Borrower Docs Sent/Req' as 'StatusName', 
       CASE WHEN t.LastModified IS NOT NULL THEN CONVERT(VARCHAR(25), t.LastModified, 126) ELSE '' END as 'StatusDate' 

      FROM [Sample] as t 
      WHERE t.[GUID] = E.[GUID] 
     ) AS s 
     FOR XML PATH('LoanSecondaryStatus'), TYPE, root('LoanSecondaryStatusDates') 
     ) 

From [Sample] E 
FOR XML PATH ('Loan'), type, root('Loans') 

sqlfiddle demo

基本上，内FOR XML PATH将返回LoanSecondaryStatus元件包裹在一个LoanSecondaryStatusDates母体，并用于产生LoanSecondaryStatus数据UNION子句。除此之外，上述查询中看到的所有转换都需要使UNION的双方返回兼容数据类型，否则查询将终止并显示转换错误。