問題描述

我在 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

這篇關于使用 Xquery 在 sql server 中查詢 XML 列表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！