如何在SQL Server中使用T-SQL从指定XML数据生成目标表格

适用SQL Server 2017及以上版本（支持STRING_AGG函数） #

直接使用STRING_AGG拼接同一客户的多条请求，写法更简洁：

Declare @Result XML
SET @Result='
<General>
  <Customer>
    <model_year>2011</model_year>
    <vehicle_id>1</vehicle_id>
    <customer_requests>
      <request>
        <definition>I want to new car tyre</definition>
      </request>
      <request>
        <definition>I want to new headlight</definition>
      </request>
    </customer_requests>
    <mileage>34000</mileage>
  </Customer>
  <Customer>
    <model_year>2012</model_year>
    <vehicle_id>2</vehicle_id>
    <customer_requests>
      <request>
        <definition>I want to general maintenance</definition>
      </request>
    </customer_requests>
    <mileage>35000</mileage>
  </Customer>
</General>
'

SELECT
    T.Customer.value('(model_year/text())[1]', 'INT') AS model_year,
    T.Customer.value('(vehicle_id/text())[1]', 'INT') AS vehicle_id,
    STRING_AGG(R.Request.value('(definition/text())[1]', 'NVARCHAR(200)'), ',') AS request,
    T.Customer.value('(mileage/text())[1]', 'INT') AS mileage
FROM @Result.nodes('/General/Customer') AS T(Customer)
CROSS APPLY T.Customer.nodes('customer_requests/request') AS R(Request)
GROUP BY
    T.Customer.value('(model_year/text())[1]', 'INT'),
    T.Customer.value('(vehicle_id/text())[1]', 'INT'),
    T.Customer.value('(mileage/text())[1]', 'INT')

适用SQL Server 2016及更早版本（无STRING_AGG函数） #

使用STUFF + FOR XML PATH实现字符串拼接，兼容性更强：

Declare @Result XML
SET @Result='
<General>
  <Customer>
    <model_year>2011</model_year>
    <vehicle_id>1</vehicle_id>
    <customer_requests>
      <request>
        <definition>I want to new car tyre</definition>
      </request>
      <request>
        <definition>I want to new headlight</definition>
      </request>
    </customer_requests>
    <mileage>34000</mileage>
  </Customer>
  <Customer>
    <model_year>2012</model_year>
    <vehicle_id>2</vehicle_id>
    <customer_requests>
      <request>
        <definition>I want to general maintenance</definition>
      </request>
    </customer_requests>
    <mileage>35000</mileage>
  </Customer>
</General>
'

SELECT
    T.Customer.value('(model_year/text())[1]', 'INT') AS model_year,
    T.Customer.value('(vehicle_id/text())[1]', 'INT') AS vehicle_id,
    STUFF((
        SELECT ',' + R.Request.value('(definition/text())[1]', 'NVARCHAR(200)')
        FROM T.Customer.nodes('customer_requests/request') AS R(Request)
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS request,
    T.Customer.value('(mileage/text())[1]', 'INT') AS mileage
FROM @Result.nodes('/General/Customer') AS T(Customer)

逻辑说明 #

• nodes()方法用于将XML节点集合拆分为行集，分别提取所有<Customer>节点、以及每个客户下的所有<request>节点
• value()方法用于从XML节点中提取指定路径的文本值，转换为对应SQL数据类型
• 两种写法最终输出结果完全匹配要求的表格格式

内容的提问来源于stack exchange，提问作者Kara