SQL Server Cleaning up XML data

xu3bshqb  于 2023-08-02  发布在  其他
关注(0)|答案(3)|浏览(106)

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

)

uajslkp6

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

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

im9ewurl2#

You can actually do this in a single scan of the base table by using STRING_AGG to aggregate a FOR XML subquery

SELECT
  PolicyNumber,
  TransactionID,
  CAST(STRING_AGG(x.Coverage, '') AS xml) AS Coverages
FROM @tbl AS t
CROSS APPLY (
    SELECT
      ClassCode AS [@CvgCode],
      Amount AS [text()]
    FOR XML PATH ('Coverage')
) x(Coverage)
GROUP BY
  PolicyNumber,
  TransactionID
FOR XML PATH ('Policy'), TYPE, ROOT('Batch');

db<>fiddle

rslzwgfq

rslzwgfq3#

You can solve it in SQL by doing something like this:

SELECT  [PolicyNumber]
,   TransactionID
,   (
    SELECT  ClassCode  AS [@CvgCode]
    ,   amount AS [*]
    FROM    ReportData r
    WHERE   r.PolicyNumber = x.policynumber
    AND r.TransactionID = x.transactionId
    FOR xml path('Coverage'), ROOT('Coverages'), TYPE
)
FROM    (
    SELECT  policynumber, TransactionID
    FROM    ReportData 
    GROUP BY PolicyNumber, TransactionID
) x
FOR XML PATH ('Policy'), ROOT('Batch'), TYPE

One pregroups the table and then creates the necessarily nodes in a subquery that gets the actual non-grouped data

相关问题