CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008sql-server-2008-r2
Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

XML AUTO Sql Server not giving proper format XML?

I'm trying to generate XML using FOR XML AUTO, I'm not getting expected XML format.

   SELECT OMR.RIDER_NAME AS RiderName,    
      /*CHANGES MADE FOR PWR RIDER DETAILS BINDING IN CPF FORM*/    
      CASE WHEN ORD.FK_RIDER_ID IN ('1','2')  THEN ORD.DHCB_RIDER_AMOUNT  ELSE ORD.SUM_ASSURED END AS SumAssured,    
      CASE WHEN ORD.FK_RIDER_ID IN ('1','2')  THEN ORD.DHCB_RIDER_TERM   ELSE ORD.POLICY_BENEFIT_PERIOD  END AS PolicyBenefitPeriod,    
      CASE WHEN ORD.FK_RIDER_ID IN ('1','2')    THEN ORD.DHCB_RIDER_TERM   ELSE ORD.PREMIUM_PAYMENT_TERM  END AS PremiumPaymentTerm          
      FROM RD ORD  INNER JOIN RM OMR ON OMR.PK_RIDER_ID = ORD.FK_RIDER_ID 
      WHERE ORD.FK_QUOTATION_ID = 78027     
      FOR XML AUTO , ROOT('RIDER')

I'm getting below xml format

<RIDER>
  <OMR RiderName="Test" SumAssured="0.00" PolicyBenefitPeriod="15" PremiumPaymentTerm="1" />
  <OMR RiderName="Test1" SumAssured="0.00" PolicyBenefitPeriod="15" PremiumPaymentTerm="1" />
</RIDER>

I want xml in below format

<RIDER>
  <OMR RiderName="test">
    <ORD SumAssured="0" PolicyBenefitPeriod="15" PremiumPaymentTerm="0" />
  </OMR>
  <OMR RiderName="test">
    <ORD SumAssured="0" PolicyBenefitPeriod="15" PremiumPaymentTerm="0" />
  </OMR>
</RIDER>

Upvotes: 1

Views: 71

Answers (2)

M.Ali
M.Ali

Reputation: 69564

Test Data

Declare @t TABLE (RiderName VARCHAR(10) , SumAssured Decimal(10,2)
                              , PolicyBenefitPeriod INT, PremiumPaymentTerm INT)
INSERT INTO @t VALUES 
('Test1' , 0.10  , 10  , 1),
('Test2' , 0.20  , 20  , 2)

Query

In the query below I have correlated the inner query on a column which is very unlikely to be the primary key column for the table, since I don't know the actual schema I just came up with this mock data and also just wanted to point out that you would need to use a primary key column in that where clause. WHERE t.PremiumPaymentTerm = PremiumPaymentTerm

SELECT t.RiderName                 AS [@RiderName]

      ,(SELECT  SumAssured         AS [@SumAssured]
              ,PolicyBenefitPeriod AS [@PolicyBenefitPeriod]
              ,PremiumPaymentTerm  AS [@PremiumPaymentTerm]
        FROM @t
        WHERE t.PremiumPaymentTerm = PremiumPaymentTerm
        FOR XML PATH('ORD'),TYPE)
FROM @t t
FOR XML PATH('OMR') , ROOT('RIDER')

Result

<RIDER>
  <OMR RiderName="Test1">
    <ORD SumAssured="0.10" PolicyBenefitPeriod="10" PremiumPaymentTerm="1" />
  </OMR>
  <OMR RiderName="Test2">
    <ORD SumAssured="0.20" PolicyBenefitPeriod="20" PremiumPaymentTerm="2" />
  </OMR>
</RIDER>

Your Query

SELECT  OMR.RIDER_NAME                              AS [@RiderName]    
     , (SELECT 
        CASE WHEN ORD.FK_RIDER_ID IN ('1','2')  
               THEN ORD.DHCB_RIDER_AMOUNT  
                ELSE ORD.SUM_ASSURED END            AS [@SumAssured]
       ,CASE WHEN ORD.FK_RIDER_ID IN ('1','2')  
               THEN ORD.DHCB_RIDER_TERM   
                ELSE ORD.POLICY_BENEFIT_PERIOD  END AS [@PolicyBenefitPeriod]
       ,CASE WHEN ORD.FK_RIDER_ID IN ('1','2')    
               THEN ORD.DHCB_RIDER_TERM   
                ELSE ORD.PREMIUM_PAYMENT_TERM  END  AS [@PremiumPaymentTerm]
    FROM  RD ORD
    WHERE OMR.PK_RIDER_ID = ORD.FK_RIDER_ID  
      AND ORD.FK_QUOTATION_ID = 78027             
    FOR XML PATH('ORD'),TYPE)
FROM RM OMR 
FOR XML PATH('OMR') , ROOT('RIDER')

Upvotes: 3

Abhishek
Abhishek

Reputation: 2490

This might work, not tested though - 

SELECT
      OMR.RIDER_NAME       AS "@RiderName",
      (SELECT OMR.RIDER_NAME AS RiderName,    
      /*CHANGES MADE FOR PWR RIDER DETAILS BINDING IN CPF FORM*/    
      CASE WHEN ORD.FK_RIDER_ID IN ('1','2')  THEN ORD.DHCB_RIDER_AMOUNT  ELSE ORD.SUM_ASSURED END AS "SumAssured",    
      CASE WHEN ORD.FK_RIDER_ID IN ('1','2')  THEN ORD.DHCB_RIDER_TERM   ELSE ORD.POLICY_BENEFIT_PERIOD  END AS "PolicyBenefitPeriod",    
      CASE WHEN ORD.FK_RIDER_ID IN ('1','2')    THEN ORD.DHCB_RIDER_TERM   ELSE ORD.PREMIUM_PAYMENT_TERM  END AS "PremiumPaymentTerm"          
      FROM RD ORD
      WHERE OMR.PK_RIDER_ID = ORD.FK_RIDER_ID
      WHERE ORD.FK_QUOTATION_ID = 78027   
      FOR XML PATH('ORD'), TYPE) AS "ORD"
      FROM RM OMR     
      FOR XML PATH('OMR') , ROOT('RIDER');

Upvotes: 0

Related Questions