問題描述

我基本上是想扭轉這個問題的要求......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......

這篇關于SQL Server 生成帶有通用字段元素的 XML的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！