SQL 数据作为 XML 元素

2021-10-01 00:00:00 xml sql-server

我对用 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.

相关文章