从 XML 更新 SQL 表

Question

我正在使用 InfoPath 2007 发送调查（它未连接到 SharePoint 或数据库）。我将返回的文件是一个 XML 文件。每个地方都有一个答案块，它有自己唯一的 id（也称为字段名称）。

现在，我有一个带有“Responses”表的 SQL Server 数据库（2007 年？）。它的列是：AnswerID（唯一的PK）、QuestionID（FK）（这是唯一的id（字段名称）和Answer。QuestionID已经填充了唯一的id（字段名称）。QuestionID有超过300条记录 。

我需要做的是进入 XML 文件，找到 QuestionID（字段名称），获取该字段名称的数据，然后将数据放入与字段名称匹配的数据库列“Answer”中 QuestionID 列。

是否有一种简单/中等的方法可以以最少的错误率进行此映射/更新？

注意：我尝试使用 DB 导入 XML 数据向导，但信息出现了问题。成数量难以管理的表。

Answer 1

您可以将 XML 分解为行和列，然后使用它来更新表。
这是您可以执行的操作的一个小示例。

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 

结果：

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

我使用的 XML 肯定与您所拥有的完全不同，但它应该提示您可以做什么。如果您发布 XML 文件、表结构和预期结果/输出的示例，您可能会得到更准确的答案。

编辑

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)

结果：

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.