問題描述

我對用 SQL 生成 XML 有點陌生.我正在嘗試從此查詢生成 XML:

I am somewhat new on generating XML out with SQL. I am trying to generate XML from this query:

SELECT RptType, DataType, Branch, ACC, Actual 
FROM ReportingTb

查詢產生以下結果:

RptType   DataType   Branch    Acc    Actual
-------   --------   -------   ----   -----------
MTD       UPS        Arizona   Total  2279.00000
MTD       UPS        Arizona   Oral   543.00000
MTD       UPS        Arizona   Tube   532.00000
MTD       UPS        Arizona   Other  1.00000

我想將前 4 列中的查詢結果實際用作 XML 元素，如下所示:

I want to use the query results in the first 4 columns the actual as XML elements like this:

<MTD>
  <UPS>
    <Arizona>
      <Total>
        <Actual>2279.00000</Actual>
      </Total>
      <Oral>
        <Actual>543.00000</Actual>
      </Oral>
      <Tube>
        <Actual>532.00000</Actual>
      </Tube>
      <Other>
        <Actual>1.00000</Actual>
      </Other>
    </Arizona>
  <UPS>
</MTD>

有人能指出我正確的方向嗎?我應該使用 T-SQL 并遍歷查詢結果以制作 XML 文件嗎?

Can someone point me in the right direction on how to do this? Should I maybe use T-SQL and loop through the results of the query in order make the XML file?

感謝您的幫助！

推薦答案

這在技術上是可行的，但由于一些原因，它確實很糟糕.相反，您應該重新考慮您的 XML 結構，使其更像這樣:

It's technically possible, but it's really bad for a few reasons. Instead, you should rethink your XML structure, to something more like this:

<Root>
  <ReportingTb RptType="MTD" DataType="UPS" Branch="Arizona">
    <Actual Acc="Total">2279.00000</Actual>
    <Actual Acc="Oral">543.00000</Actual>
    <Actual Acc="Tube">532.00000</Actual>
    <Actual Acc="Other">1.00000</Actual>
  </ReportingTb>
</Root>

使用此查詢:

DECLARE @t TABLE (RptType varchar(20),   DataType VARCHAR(20),   Branch VARCHAR(20),    Acc VARCHAR(20),   Actual numeric(10,5));
INSERT @t VALUES
('MTD',  'UPS'        ,'Arizona',   'Total',  2279.00000)
,('MTD',  'UPS'        ,'Arizona',   'Oral',  543.00000)
,('MTD',  'UPS'        ,'Arizona',   'Tube',  532.00000)
,('MTD',  'UPS'        ,'Arizona',   'Other',  1.00000);

SELECT RptType as '@RptType'
    ,DataType as '@DataType'
    ,Branch as '@Branch'
    ,(SELECT 
         Acc AS '@Acc'
        ,Actual as '*'
    FROM @t t2
    WHERE t2.RptType = t1.RptType AND t2.DataType = t1.DataType AND t2.Branch = t1.Branch
    FOR XML PATH('Actual'), TYPE)
FROM @t t1
GROUP BY RptType, DataType, Branch
FOR XML PATH('ReportingTb'), ROOT('Root');

這為您提供了可以根據模式(而不是必須包含許多可能的節點名稱的模式)進行合理驗證的 XML.它將更自然地處理不同的分組可能性，并將處理 FOR XML 在幕后處理的所有 XML 怪異現象.

This gives you XML that can be sensibly validated against a schema (instead of a schema that has to contain many possible node names). It will handle different grouping possibilities more naturally, and it will take care of all the XML weirdness that FOR XML handles behind the scenes.

也就是說，技術上可以實現你最初的愿望；這是一個可以做到的查詢:

That said, it is technically possible to achieve your original desire; here's a query that would do it:

SELECT 
    CAST('<' + RptType + '>'
    + (SELECT 
        '<' + DataType + '>' 
        + (SELECT
            '<' + Branch + '>'
            + REPLACE(REPLACE(
            (SELECT
                 '#' + acc + '!' as '*' , Actual, '#/' + acc + '!' as '*'
                FROM @t t4 WHERE t4.Branch = t3.Branch AND t4.DataType = t2.DataType AND t4.RptType = t1.RptType
                FOR XML PATH('')), '#', '<'), '!', '>')

            + '</' + Branch + '>'
            FROM  @t t3  WHERE t3.DataType = t2.DataType AND t3.RptType = t1.RptType GROUP BY Branch)
        + '</' + DataType + '>'
        FROM @t t2  WHERE t2.RptType = t1.RptType GROUP BY DataType)
    + '</' + RptType + '>'  AS XML)
FROM @t t1
GROUP BY RptType

請注意，您不應該這樣做，這真的很丑陋(我只是向您展示，讓您了解即使參與其中也有多么丑陋).它真的只會變得更糟.我正在使用奇怪的字符串替換，可能會或可能不會在野外工作.最重要的是，如果任何節點具有無效的 XML 字符，則必須對其進行轉義 - 可能會添加一些額外的替換 (REPLACE(col, '<', '&lt;')) 或其他東西，但這又是丑陋的，并且必須對幾個值重復一遍.您基本上是將 SQL Server 用作 XML 編寫器，而實際上并不是要這樣做.如果你絕對必須有這個結構，那么你應該將數據傳遞給一個CLR類，該類可以正確使用XmlWriter或XDocument之類的東西來編寫使用這些值的實際 XML.您甚至可以將 CLR 類放在 SQL Server 中并從存儲過程中調用它.

Note that you should not do this, it's really ugly hackery (I'm only showing you to give you an idea of how ugly even to get part of the way there). It really only gets worse. I'm using weird string replaces that may or may not work in the wild. On top of that, if any node has an invalid XML character, you'd have to escape it - maybe throw in some additional replaces (REPLACE(col, '<', '&lt;')) or something, but again that's ugly and has to be repeated all over for several values. You're basically using SQL Server as an XML Writer, and it's really not meant to do that. If you absolutely must have this structure, then you should pass the data to a CLR class that can properly use something like XmlWriter or XDocument to write the actual XML using these values. You could even put the CLR class in SQL Server and call it from the stored procedure.

這篇關于SQL 數據作為 XML 元素的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！