Запрос списка XML на сервере sql с использованием Xquery

Question

У меня есть таблица на сервере SQL, которая используется для хранения отправленных данных формы. Поля формы для каждой отправки являются динамическими, поэтому собранные данные хранятся в виде пар имя-значение в столбце данных XML с именем [formdata], как в примере ниже...

Это прекрасно работает для сбора необходимой информации, но теперь мне нужно представить эти данные в виде плоского файла или документа Excel для обработки сотрудниками, и мне интересно, как лучше всего это сделать с помощью Xquery, чтобы данные были читабельными?

Таблица выглядит следующим образом...

[id], [user_id], [datestamp], [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>

Answer 1

Это то, что вы хотите?

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