CODEWITHSUNDEEP
sql-serverxml
dc922
dc922

Reputation: 639

For XML structure with two parent nodes and multiple child nodes?

I need the XML to be structured like this: 

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

I have a FOR XML query that is structured like this: 

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

This is getting me close, but is missing the top "LoanSecondaryStatusDates" root node. How can I add this root node in? http://sqlfiddle.com/#!6/d672a/2/0

Note: I was able to add the node using this 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>

Upvotes: 1

Views: 2501

Answers (1)

har07
har07

Reputation: 89305

This is one possible SQL query, built according to your SQL Fiddle table structure, that will produce the desired XML format :

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

Basically, the inner FOR XML PATH will return LoanSecondaryStatus elements wrapped in one LoanSecondaryStatusDates parent, and a UNION clause used to generate LoanSecondaryStatus data. Apart from that, all the conversions seen in the above query are needed to make both sides of the UNION return compatible data types, otherwise the query will terminates with conversion error.

Upvotes: 1

Related Questions