如何在 T-SQL 中透视 XML 列的属性 [英] How do I Pivot on an XML column's attributes in T-SQL
问题描述
我需要对表中的 XML 列执行数据透视,其中 XML 包含具有多个属性的多个元素.每个元素中的属性总是相同的,但是元素的数量会有所不同.让我举个例子...
I need to perform a pivot on an XML column in a table, where the XML contains multiple elements with a number of attributes. The attributes in each element is always the same, however the number of elements will vary. Let me give an example...
FormEntryId | FormXML | DateCreated
====================================================================================
1 |<Root> | 10/15/2009
| <Form> |
| <FormData FieldName="Username" FieldValue="stevem" /> |
| <FormData FieldName="FirstName" FieldValue="Steve" /> |
| <FormData FieldName="LastName" FieldValue="Mesa" /> |
| </Form> |
|</Root> |
| |
------------------------------------------------------------------------------------
2 |<Root> | 10/16/2009
| <Form> |
| <FormData FieldName="Username" FieldValue="bobs" /> |
| <FormData FieldName="FirstName" FieldValue="Bob" /> |
| <FormData FieldName="LastName" FieldValue="Suggs" /> |
| <FormData FieldName="NewField" FieldValue="test" /> |
| </Form> |
|</Root> |
我需要为每个不同的 FieldName 属性值(在本例中为 Username、FirstName、LastName 和 NewField)得到一个结果集,并将它们对应的 FieldValue 属性作为值.我上面给出的示例的结果如下所示:
I need to wind up with a result set for each distinct FieldName attribute values (In this example, Username, FirstName, LastName, and NewField) with their corresponding FieldValue attributes as the value. The results for the example I gave above would look like:
FormEntryId | Username | FirstName | LastName | NewField | DateCreated
======================================================================
1 | stevem | Steve | Mesa | NULL | 10/15/2009
----------------------------------------------------------------------
2 | bobs | Bob | Suggs | test | 10/16/2009
我已经找到了一种使用静态列完成此操作的方法
I've figured out a way to accomplish this with static columns
SELECT
FormEntryId,
FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="Username"][1]/@FieldValue','varchar(max)') AS Username,
FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="FirstName"][1]/@FieldValue','varchar(max)') AS FirstName,
FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="LastName"][1]/@FieldValue','varchar(max)') AS LastName,
FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="NewField"][1]/@FieldValue','varchar(max)') AS NewField,
DateCreated
FROM FormEntry
但是,我想看看是否有一种方法可以根据不同的FieldName"属性值集使列动态化.
However I would like to see if there's a method to have the columns be dynamic based on the distinct set of "FieldName" attribute values.
推荐答案
看看 这个动态数据透视 以及最近的这个 - 您基本上需要能够SELECT DISTINCT FieldName
使用此技术动态构建您的查询.
Have a look at this dynamic pivot and more recently this one - you basically need to be able to SELECT DISTINCT FieldName
to use this technique to build your query dynamically.
以下是您的特定问题的完整答案(请注意,在知道列应出现的顺序时,从不同属性生成列表时存在列顺序弱点):
Here's the full answer for your particular problem (note that there is a column order weakness when generating the list from the distinct attributes in knowing what order the columns should appear):
DECLARE @template AS varchar(MAX)
SET @template = 'SELECT
FormEntryId
,{@col_list}
,DateCreated
FROM FormEntry'
DECLARE @col_template AS varchar(MAX)
SET @col_template = 'FormXML.value(''/Root[1]/Form[1]/FormData[@FieldName="{FieldName}"][1]/@FieldValue'',''varchar(max)'') AS {FieldName}'
DECLARE @col_list AS varchar(MAX)
;WITH FieldNames AS (
SELECT DISTINCT FieldName
FROM FormEntry
CROSS APPLY (
SELECT X.FieldName.value('@FieldName', 'varchar(255)')
FROM FormXML.nodes('/Root[1]/Form[1]/FormData') AS X(FieldName)
) AS Y (FieldName)
)
SELECT @col_list = COALESCE(@col_list + ',', '') + REPLACE(@col_template, '{FieldName}', FieldName)
FROM FieldNames
DECLARE @sql AS varchar(MAX)
SET @sql = REPLACE(@template, '{@col_list}', @col_list)
EXEC (@sql)
这篇关于如何在 T-SQL 中透视 XML 列的属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!