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
)
3条答案
按热度按时间uajslkp61#
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
Output XML
im9ewurl2#
You can actually do this in a single scan of the base table by using
STRING_AGG
to aggregate aFOR XML
subquerydb<>fiddle
rslzwgfq3#
You can solve it in SQL by doing something like this:
One pregroups the table and then creates the necessarily nodes in a subquery that gets the actual non-grouped data