使用SQL Server将表格数据转换为XML

I have a flattened table that contains columns that represent groups that need to be displayed in XML. Example data:

我有一个展平的表,其中包含表示需要以XML格式显示的组的列。示例数据:

Market, Label, Style, Type
XXX,    YYY,   JJJ,   111
XXX,    YYY,   JJJ,   222
XXX,    YYY,   JJJ,   333
XXX,    YYY,   JJJ,   444    
XXX,    YYY,   LLL,   111    
XXX,    YYY,   LLL,   222    
XXX,    YYY,   LLL,   333    
XXX,    YYY,   LLL,   444

Using T-SQL what would be the best way to output the following:

使用T-SQL将是输出以下内容的最佳方式:

<Market value=XXX>
    <label value=YYY>
       <Style value=JJJ>
          <Type value=111>
          </Type>
          ...
       </Style>
       <Style value=LLL>
          ...
       </Style>
    </label>
</Market>

Can I do this by using the XML Explicit clause in SQL Server?

我可以通过在SQL Server中使用XML Explicit子句来完成此操作吗?

4 个解决方案

#1

Formatting complex XML documents in T-SQL is a fool's errand. It can be done - maybe - but then you come back to it a month later and what you've got is incomprehensible.

在T-SQL中格式化复杂的XML文档是一个愚蠢的错误。可以这样做 - 也许 - 但是一个月之后你又回到了它,而你所拥有的是难以理解的。

It's much, much easier to either write a method in C# or whatever that processes a DataReader to produce the XML, or write an XSLT transform that converts the XML emitted from the query into whatever specialized format you're trying to create.

在C#中编写方法或者处理DataReader以生成XML,或编写将查询发出的XML转换为您尝试创建的任何专用格式的XSLT转换要容易得多。

#2

This might help.. take a look

这可能会有所帮助..看一看

SELECT distinct MyTable.Market "Market/@Value",
        MyTable.Label "Market/Label/@Value",
        MyTable.Style "Market/Label/Style/@Value",

             (SELECT Type AS  "Value"
               FROM   MyTable myTab
               WHERE myTab.Market=MyTable.Market
                        and myTab.Label=MyTable.Label
                        and myTab.Style = MyTable.Style
               FOR XML PATH ('')
               ) AS "Market/Label/Style/Type"
        FROM MyTable  
FOR XML PATH('')

Resultant XML was :

结果XML是:

<Market Value="XXX">
  <Label Value="YYY">
    <Style Value="JJJ">
      <Type>&lt;Value&gt;111&lt;/Value&gt;&lt;Value&gt;222&lt;/Value&gt;&lt;Value&gt;333&lt;/Value&gt;&lt;Value&gt;444&lt;/Value&gt;</Type>
    </Style>
  </Label>
</Market>
<Market Value="XXX">
  <Label Value="YYY">
    <Style Value="LLL">
      <Type>&lt;Value&gt;111&lt;/Value&gt;&lt;Value&gt;123&lt;/Value&gt;</Type>
    </Style>
  </Label>
</Market>

Hope it helps

希望能帮助到你

#3

there are 3 shaping method to generate table to xml in SQL Server 2000:

在SQL Server 2000中有3个生成表到xml的整形方法:

for xml auto -> generate the query to xml automatically based on using table

for xml auto - >根据使用表自动生成对xml的查询

for xml raw -> generate the query table to row tag in xml

for xml raw - >在xml中生成查询表到行标记

for xml explicit -> the most complex but most comfort way :) you can defined your own xml structure

对于xml显式 - >最复杂但最舒适的方式:)你可以定义自己的xml结构

for the cheat sheet you can see this link

对于备忘单,您可以看到此链接

hope this can help you.

希望这可以帮到你。

#4

Have a look at the SQLXML library. You can create an annotated XSD file that will produce an XML document.

看看SQLXML库。您可以创建一个带注释的XSD文件,该文件将生成XML文档。

http://msdn.microsoft.com/en-us/library/ms172699

Just be forewarned that the performance of this method is quite bad. I have made multiple requests to Microsoft to look at this issue. I have compared hand-writing a "FOR XML" query that takes milliseconds to finish, and the annotated XSD version takes 10 seconds.

只是预先警告这种方法的表现非常糟糕。我已向Microsoft提出多个请求以查看此问题。我比较了手写一个“FOR XML”查询,需要几毫秒才能完成,带注释的XSD版本需要10秒。

I personally think that annotated XSDs are a very elegant solution if you can work around the poor performance.

我个人认为,如果您可以解决性能不佳的问题,带注释的XSD是一种非常优雅的解决方案。

#1