Querying An Xml List In Sql Server Using Xquery

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 XM

Solution 1:

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') asf(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:

iduser_iddatestampitemKeyitemValue112011-05-23 15:38:55.673  USER_NAMEtest112011-05-23 15:38:55.673  value2test112011-05-23 15:38:55.673  MYID5468512112011-05-23 15:38:55.673  testcheckboxitem1,item3112011-05-23 15:38:55.673  samplevalueitem3112011-05-23 15:38:55.673  accept_termsTrue