如何在XML存储过程中求和子节点列 [英] How to Sum child node column in XML Stored Procedure

问题描述

嗨朋友们，



我的情况是我在查询数据库并返回XML。



这是当前的输出，

Hi Friends,

I have a situation where I am querying the Database and returning an XML.

Here is the current output,

<clientaccount>
     <clientname>abc</clientname>
     <maskedtin></maskedtin>
     <accounttype>abc</accounttype>
     <registrationtype>abc</registrationtype>
     <marketvalue>123</marketvalue>
     <clientaccountpositionsitems>
       <clientaccountpositions>
         <noofshares>1</noofshares>
         <price>1</price>
         <marginbalance>1</marginbalance>
         <cashbalance>1</cashbalance>
       </clientaccountpositions>
       <clientaccountpositions>
         <noofshares>1</noofshares>
         <price>2</price>
         <marginbalance>2</marginbalance>
         <cashbalance>2</cashbalance>
       </clientaccountpositions>
     </clientaccountpositionsitems>
   </clientaccount>

这是我的PROC，

Here is my PROC,

ALTER PROCEDURE [dbo].[usp_Client_AccountPositions] @AccountId INT
       AS
       BEGIN
         DECLARE @XmlResult XML = NULL,
           @AccountPartyId INT;

         SELECT @AccountPartyId = dbo.fns_AccountParty_PrimaryAccountByAccountId(@AccountId);

           -- Get data
         SET @XmlResult = (SELECT LIBRARY.dbo.fns_LNameFNameMName(ap.LastName, ap.FirstName, ap.MiddleName) AS ClientName,
                               LIBRARY.dbo.fns_MaskSSN(ap.CleanTIN, 1) AS MaskedTIN,
                               latc.[Description] AS AccountType,
                               lat.[Description] AS RegistrationType,
                               m.MarketValue,
                               (SELECT (SELECT p.TotalQuantity AS NoOfShares,
                                           s.CurrentPrice AS Price,
                                           s.CurrentPriceDate,
                                           p.MarginQuantity * s.CurrentPrice AS MarginBalance,
                                           p.CashQuantity * s.CurrentPrice AS CashBalance,
                                         FROM (SELECT p2.SecurityID, p2.EffectiveDate,
                                                   SUM(p2.Quantity) AS TotalQuantity,
                                                   ISNULL(SUM(CASE WHEN p2.PositionTypeID = 2 THEN p2.Quantity
                                                              END), 0) AS MarginQuantity,
                                                   SUM(CASE WHEN p2.PositionTypeID <> 2 THEN p2.Quantity
                                                       END) AS CashQuantity
                                                 FROM dbo.fnt_Account_PositionList(@AccountId) pl
                                                                                               JOIN dbo.Position p2 WITH (NOLOCK) ON p2.PositionId = pl.PositionId
                                                 GROUP BY p2.SecurityID, p2.EffectiveDate) p
                                           LEFT JOIN dbo.Security s WITH (NOLOCK) ON p.SecurityID = s.SecurityId
                                   FOR XML PATH('ClientAccountPositions'),
                                            TYPE)
                               FOR XML PATH('ClientAccountPositionsItems'),
                                    TYPE)
                             FROM dbo.Account a WITH (NOLOCK)
                               JOIN dbo.AccountParty ap WITH (NOLOCK) ON ap.AccountPartyId = @AccountPartyId
                               JOIN dbo.ctlAccountType lat WITH (NOLOCK) ON a.AccountTypeID = lat.AccountTypeId
                               JOIN dbo.ctlAccountCategory latc WITH (NOLOCK) ON lat.AccountCategoryId = latc.AccountCategoryId
                               LEFT JOIN dbo.AccountMarketValue m WITH (NOLOCK) ON a.AccountId = m.AccountID
                             WHERE a.AccountId = @AccountId
                               AND a.IsActive = 1
                               AND a.IsHidden = 0
                               AND a.CloseDate IS NULL
                                       FOR XML PATH('ClientAccount'),
                               TYPE);

         -- Return results
         SELECT CAST(@XmlResult AS VARCHAR(MAX)) AS XMLResult;

所以我需要更新我的Proc才能返回一个XML给我回报

So I need to Update my Proc in order to return an XML that would return me

<clientaccount>
      <clientname>abc</clientname>
      <maskedtin></maskedtin>
      <accounttype>abc</accounttype>
      <registrationtype>abc</registrationtype>
      <marketvalue>123</marketvalue>
      <totalmarginbalance>3</totalmarginbalance>
      <totalcashbalance>3</totalcashbalance>
      <clientaccountpositionsitems>
        <clientaccountpositions>
          <noofshares>1</noofshares>
          <price>1</price> 
        </clientaccountpositions>
        <clientaccountpositions>
          <noofshares>1</noofshares>
          <price>2</price>     
        </clientaccountpositions>
      </clientaccountpositionsitems>
    </clientaccount>

谢谢！

Thanks!

推荐答案

这是您要等待的代码请试试

Here is the code you are waiting please try it

 ALTER PROCEDURE [dbo].[usp_Client_AccountPositions] @AccountId INT
        AS
        BEGIN
          DECLARE @XmlResult XML = NULL,
            @AccountPartyId INT;
            
          SELECT @AccountPartyId = dbo.fns_AccountParty_PrimaryAccountByAccountId(@AccountId);
        
        	-- Get data



DECLARE @XmlResult XML = NULL,
            @AccountPartyId INT;
            
          SELECT @AccountPartyId = dbo.fns_AccountParty_PrimaryAccountByAccountId(@AccountId);
        
        	-- Get data
          SET @XmlResult = (SELECT LIBRARY.dbo.fns_LNameFNameMName(ap.LastName, ap.FirstName, ap.MiddleName) AS ClientName,
                                LIBRARY.dbo.fns_MaskSSN(ap.CleanTIN, 1) AS MaskedTIN,
                                latc.[Description] AS AccountType,
                                lat.[Description] AS RegistrationType,
                                m.MarketValue,
                                sum(p.MarginQuantity * s.CurrentPrice) totalmarginbalance,--Change
                                sum(p.CashQuantity * s.CurrentPrice) totalcashbalance,--Change
                                (SELECT (SELECT p.TotalQuantity AS NoOfShares,
                                            s.CurrentPrice AS Price,                                    
                                            s.CurrentPriceDate,                                    
                                            --p.MarginQuantity * s.CurrentPrice AS MarginBalance, --Change
                                            --p.CashQuantity * s.CurrentPrice AS CashBalance,             --Change                     
                                          FROM (SELECT p2.SecurityID, p2.EffectiveDate,
                                                    SUM(p2.Quantity) AS TotalQuantity,
                                                    ISNULL(SUM(CASE WHEN p2.PositionTypeID = 2 THEN p2.Quantity
                                                               END), 0) AS MarginQuantity,
                                                    SUM(CASE WHEN p2.PositionTypeID <> 2 THEN p2.Quantity
                                                        END) AS CashQuantity
                                                  FROM dbo.fnt_Account_PositionList(@AccountId) pl
        																						JOIN dbo.Position p2 WITH (NOLOCK) ON p2.PositionId = pl.PositionId
                                                  GROUP BY p2.SecurityID, p2.EffectiveDate) p
                                            LEFT JOIN dbo.Security s WITH (NOLOCK) ON p.SecurityID = s.SecurityId
                                    FOR XML PATH('ClientAccountPositions'),
                                             TYPE)
                                FOR XML PATH('ClientAccountPositionsItems'),
                                     TYPE)
                              FROM dbo.Account a WITH (NOLOCK)
                                JOIN dbo.AccountParty ap WITH (NOLOCK) ON ap.AccountPartyId = @AccountPartyId
                                JOIN dbo.ctlAccountType lat WITH (NOLOCK) ON a.AccountTypeID = lat.AccountTypeId
                                JOIN dbo.ctlAccountCategory latc WITH (NOLOCK) ON lat.AccountCategoryId = latc.AccountCategoryId
                                LEFT JOIN dbo.AccountMarketValue m WITH (NOLOCK) ON a.AccountId = m.AccountID
                              WHERE a.AccountId = @AccountId
                                AND a.IsActive = 1
                                AND a.IsHidden = 0
                                AND a.CloseDate IS NULL
        								FOR XML PATH('ClientAccount'),
                                TYPE);

这篇关于如何在XML存储过程中求和子节点列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！