Updating SQL table from XML

I am using InfoPath 2007 to send out a survey (it is not connected to SharePoint or a DataBase). The file I will get back is an XML file. Every place there is an answer block, it has its own unique id (aka field name).

Now, I have a SQL Server Database (2007?) with a table "Responses". Its columns are: AnswerID(unique PK), QuestionID (FK) (which is the unique id (field name), and Answer. The QuestionID is already populated with the unique id (field name). There are more than 300 records for QuestionID.

What I need to be able to do is reach into the XML file, find the QuestionID (field name), grab the data for that field name, and then put the data into the DB column "Answer" that matches the field name in the QuestionID column.

Is there an easy/medium way to do this mapping/updating with the least amount of chance of开发者_高级运维 error?

NOTE: I tried to use the DB import XML data wizard, the information breaks out into an unmanageable number of tables.

You can shred the XML into rows and columns and then use that to update your table. Here is a little example of what you can do.

create table Responses(QuestionID varchar(10), Answer varchar(10))

insert into Responses values('Q1', null)
insert into Responses values('Q2', null)
insert into Responses values('Q3', null)

declare @xml xml
set @xml = 
'<root>
  <question ID="Q1">Answer1</question>
  <question ID="Q2">Answer2</question>
  <question ID="Q3">Answer3</question>
 </root>'

;with cte as
(
  select 
    r.n.value('@ID', 'varchar(10)') as QuestionID,
    r.n.value('.', 'varchar(10)') as Answer
  from @xml.nodes('/root/*') as r(n)
)
update R
set Answer = C.Answer
from Responses as R
  inner join cte as C
    on R.QuestionID = C.QuestionID

select *
from Responses 

Result:

QuestionID Answer
---------- ----------
Q1         Answer1
Q2         Answer2
Q3         Answer3

The XML I used most certainly does not look anything like what you have but it should give you a hint of what you can do. If you post a sample of your XML file, table structure and expected result/output you can probably get a more precise answer.

Edit

declare @xml xml = 
'<?xml version="1.0" encoding="UTF-8"?>
<my:myFields xmlns:my="xx.com" xml:lang="en-us">
  <my:group1>
    <my:group2>
      <my:field1>Im an analyst.</my:field1>
      <my:group3>
        <my:group4>
          <my:field2>1</my:field2>
          <my:field3>I click the mouse.</my:field3>
        </my:group4>
        <my:group4>
          <my:field2>2</my:field2>
          <my:field3>I type on the keyboard.</my:field3>
        </my:group4>
      </my:group3>
    </my:group2>
    <my:group2>
      <my:field1>Im a stay at home mom.</my:field1>
      <my:group3>
        <my:group4>
          <my:field2>1</my:field2>
          <my:field3>I Cook.</my:field3>
        </my:group4>
        <my:group4>
          <my:field2>2</my:field2>
          <my:field3>I clean.</my:field3>
        </my:group4>
      </my:group3>
    </my:group2>
  </my:group1>
</my:myFields>'

;with xmlnamespaces('xx.com' as my)
select 
  T.N.value('../../my:field1[1]', 'varchar(50)') as Field1,
  T.N.value('my:field2[1]', 'varchar(50)') as Field2,
  T.N.value('my:field3[1]', 'varchar(50)') as Field3
from @xml.nodes('my:myFields/my:group1/my:group2/my:group3/my:group4') as T(N)

Result:

Field1                 Field2       Field3
Im an analyst.          1           I click the mouse.
Im an analyst.          2           I type on the keyboard.
Im a stay at home mom.  1           I Cook.
Im a stay at home mom.  2           I clean.