使用 Xquery 在 sql server 中查询 XML 列表

2021-10-02 00:00:00 xml xpath xquery sql-server xquery-sql

我在 SQL Server 中有一个表，用于存储提交的表单数据.每次提交的表单字段都是动态的，因此收集的数据作为名称值对存储在名为 [formdata] 的 XML 数据列中，如下例所示...

I have a table in SQL server that is used to store submitted form data. The form fields for each submission are dynamic so the collected data is stored as name value pairs in an XML data column called [formdata] as in the example below...

这可以很好地收集所需的信息，但我现在需要将这些数据呈现到平面文件或 Excel 文档中以供工作人员处理，我想知道使用 Xquery 的最佳方法是什么，以便数据可读吗?

This works fine for collecting the required information but I now need to render this data to a flat file or an excel document for processing by human staff members and im wondering what the best way of doing this would be using Xquery so that the data is readable?

表格如下...

[id], [user_id], [datestamp], [formdata]

以及 formdata 的示例值

And a sample value for formdata 

<formfields>
  <item>
    <itemKey>USER_NAME</itemKey>
    <itemValue>test</itemValue>
  </item>
  <item>
    <itemKey>value2</itemKey>
    <itemValue>test</itemValue>
  </item>
  <item>
    <itemKey>MYID</itemKey>
    <itemValue>5468512</itemValue>
  </item>
  <item>
    <itemKey>testcheckbox</itemKey>
    <itemValue>item1,item3</itemValue>
  </item>
  <item>
    <itemKey>samplevalue</itemKey>
    <itemValue>item3</itemValue>
  </item>
  <item>
    <itemKey>accept_terms</itemKey>
    <itemValue>True</itemValue>
  </item>
</formfields>

推荐答案

这是您想要的吗?

select
  id,
  [user_id],
  datestamp,
  f.i.value('itemKey[1]', 'varchar(50)') as itemKey,
  f.i.value('itemValue[1]', 'varchar(50)') as itemValue
from YourTable as T
  cross apply T.formdata.nodes('/formfields/item') as f(i)

测试:

declare @T table
(
  id int,
  user_id int,
  datestamp datetime,
  formdata xml
)

insert into @T (id, user_id, datestamp, formdata)
values (1, 1, getdate(),
'<formfields>
  <item>
    <itemKey>USER_NAME</itemKey>
    <itemValue>test</itemValue>
  </item>
  <item>
    <itemKey>value2</itemKey>
    <itemValue>test</itemValue>
  </item>
  <item>
    <itemKey>MYID</itemKey>
    <itemValue>5468512</itemValue>
  </item>
  <item>
    <itemKey>testcheckbox</itemKey>
    <itemValue>item1,item3</itemValue>
  </item>
  <item>
    <itemKey>samplevalue</itemKey>
    <itemValue>item3</itemValue>
  </item>
  <item>
    <itemKey>accept_terms</itemKey>
    <itemValue>True</itemValue>
  </item>
</formfields>
'
)

select
  id,
  [user_id],
  datestamp,
  f.i.value('itemKey[1]', 'varchar(50)') as itemKey,
  f.i.value('itemValue[1]', 'varchar(50)') as itemValue
from @T as T
  cross apply T.formdata.nodes('/formfields/item') as f(i)

结果:

id  user_id  datestamp                itemKey       itemValue
1   1        2011-05-23 15:38:55.673  USER_NAME     test
1   1        2011-05-23 15:38:55.673  value2        test
1   1        2011-05-23 15:38:55.673  MYID          5468512
1   1        2011-05-23 15:38:55.673  testcheckbox  item1,item3
1   1        2011-05-23 15:38:55.673  samplevalue   item3
1   1        2011-05-23 15:38:55.673  accept_terms  True

相关文章