問題描述

我對(duì)用 SQL 生成 XML 有點(diǎn)陌生.我正在嘗試從此查詢生成 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

查詢產(chǎn)生以下結(jié)果:

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 列中的查詢結(jié)果實(shí)際用作 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>

有人能指出我正確的方向嗎?我應(yīng)該使用 T-SQL 并遍歷查詢結(jié)果以制作 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?

感謝您的幫助！

推薦答案

這在技術(shù)上是可行的，但由于一些原因，它確實(shí)很糟糕.相反，您應(yīng)該重新考慮您的 XML 結(jié)構(gòu)，使其更像這樣:

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');

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

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.

也就是說，技術(shù)上可以實(shí)現(xiàn)你最初的愿望；這是一個(gè)可以做到的查詢:

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

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

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.

這篇關(guān)于SQL 數(shù)據(jù)作為 XML 元素的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！