Cleaning up XML data

Question

I have a project that requires a XML data feed and I am not an XML programmer...

I have figured out the SQL that creates the following results:

<Batch>
  <Policy>
    <PolicyNumber>2134</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">50.00</Coverage>
    </Coverages>
  </Policy>
  <Policy>
    <PolicyNumber>2134/PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1235">30.00</Coverage>
    </Coverages>
  </Policy>
  <Policy>
    <PolicyNumber>3124</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">10.00</Coverage>
    </Coverages>
  </Policy>
</Batch>

... and I need to format it to look like:

<Batch>
  <Policy>
    <PolicyNumber>2134</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">50.00</Coverage>
      <Coverage CvgCode="1235">30.00</Coverage>
    </Coverages>
  </Policy>
  <Policy>
    <PolicyNumber>3124</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">10.00</Coverage>
    </Coverages>
  </Policy>
</Batch>

I guess I need a XSL style sheet to combine the nodes, but I don't know where to start. Any help would be appreciated. TIA.

(The SQL code for the data set...

CREATE Table ReportData (
    PolicyNumber varchar(20) null
    ,TransactionID int null
    ,ClassCode varchar(5) null
    ,Amount money null
)

INSERT INTO ReportData
VALUES
('2134',1,'1234',50.00)
,('2134',1,'1235',30.00)
,('3124',1,'1234',10.00)

SELECT 
    [PolicyNumber] 
    ,TransactionID
    ,ClassCode "Coverages/Coverage/@CvgCode"
    ,Amount "Coverages/Coverage"
FROM ReportData 
FOR XML PATH ('Policy'), ROOT('Batch'), ELEMENTS

)

Answer 1

There is no need for XSLT in your case.

SQL Server's XQuery is powerful enough to compose desired XML in one shot for your task.

SQL

-- DDL and sample data population, start
DECLARE @tbl Table (
    PolicyNumber varchar(20) null
    ,TransactionID int null
    ,ClassCode varchar(5) null
    ,Amount money null
)
INSERT INTO @tbl VALUES
('2134',1,'1234',50.00)
,('2134',1,'1235',30.00)
,('3124',1,'1234',10.00)
-- DDL and sample data population, end

SELECT PolicyNumber, TransactionID
    , (
        SELECT ClassCode AS [@CvgCode]
            , Amount AS [text()]
        FROM @tbl AS c  -- child
        WHERE c.PolicyNumber = p.PolicyNumber
        FOR XML PATH ('Coverage'), TYPE, ROOT('Coverages')
)
FROM @tbl AS p  -- parent
GROUP BY PolicyNumber,TransactionID
FOR XML PATH ('Policy'), TYPE, ROOT('Batch')

Output XML

<Batch>
  <Policy>
    <PolicyNumber>2134</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">50.0000</Coverage>
      <Coverage CvgCode="1235">30.0000</Coverage>
    </Coverages>
  </Policy>
  <Policy>
    <PolicyNumber>3124</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">10.0000</Coverage>
    </Coverages>
  </Policy>
</Batch>