querying an XML list in sql server using Xquery

Question

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...

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?

The table is as follows...

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

Mikael Eriksson Mikael Eriksson · Accepted Answer · 2011-05-23T13:39:44

Is this what you want?

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)

Test:

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)

Result:

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

querying an XML list in sql server using Xquery

1 Answers