SQL Server 生成带有通用字段元素的 XML

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

我基本上是想扭转这个问题的要求......SQL Server 查询元素值的 xml 属性

I'm basically trying to reverse what this question is asking... SQL Server query xml attribute for an element value

我需要生成一个row"元素的结果集，其中包含一组field"元素，其属性定义了键.

I need to produce a result set of "row" elements that contain a group of "field" elements with an attribute that defines the key.

<resultset statement="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
    <field name="id">1</field>
    <field name="version">0</field>
    <field name="property">My Movie</field>
    <field name="release_date">2012-01-01</field>
    <field name="territory_code">FR</field>
    <field name="territory_description">FRANCE</field>
    <field name="currency_code">EUR</field>
</row>
<row>
    <field name="id">2</field>
    <field name="version">0</field>
    <field name="property">My Sequel</field>
    <field name="release_date">2014-03-01</field>
    <field name="territory_code">UK</field>
    <field name="territory_description">United Kingdom</field>
    <field name="currency_code">GBP</field>
</row>
</resultset>

我有一个查询返回这个...

I've got a query that returns this...

<resultset statement="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
    <id>1</id>
    <version>0</version>
    <property>My Movie</property>
    <release_date>2012-01-01</release_date>
    <territory_code>FR</territory_code>
    <territory_description>FRANCE</territory_description>
    <currency_code>EUR</currency_code>
</row>
<row>
    <id>2</id>
    <version>0</version>
    <property>My Sequel</property>
    <release_date>2014-03-01</release_date>
    <territory_code>UK</territory_code>
    <territory_description>UNITED KINGDOM</territory_description>
    <currency_code>GBP</currency_code>
</row>
</resultset>

在我的 SQL 语句中使用 FOR XML PATH ('row'), ROOT ('resultset').

Using FOR XML PATH ('row'), ROOT ('resultset') in my SQL statement.

我错过了什么?谢谢.

推荐答案

它有点涉及 SQL Server - 正常行为就是您所看到的 - 列名称将用作 XML 元素名称.

It's a bit involved in SQL Server - the normal behavior is what you're seeing - the column names will be used as XML element names.

如果您真的希望所有 XML 元素都具有相同的名称，则必须使用如下代码:

If you really want all XML elements to be named the same, you'll have to use code something like this:

SELECT
    'id' AS 'field/@name',
    id AS 'field',
    '',
    'version' AS 'field/@name',
    version AS 'field',
    '',
    'property' AS 'field/@name',
    property AS 'field',
    '',
    ... and so on ....
FROM Person.Person
FOR XML PATH('row'),ROOT('resultset')

这是必要的，以确保将列名用作 元素上的 name 属性，并且需要空字符串，以便 SQLXML 解析器不会混淆哪个 name 属性属于哪个元素......

This is necessary to make sure the column name is used as the name attribute on the <field> element, and the empty string are necessary so that the SQL XML parser doesn't get confused about which name attribute belongs to what element......

相关文章