从 XML 更新 SQL 表 [英] Updating SQL table from XML

问题描述

我使用 InfoPath 2007 发送调查(未连接到 SharePoint 或数据库).我将返回的文件是一个 XML 文件.每个地方都有一个答案块，它有自己唯一的 id(又名字段名).

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

现在，我有一个带有响应"表的 SQL Server 数据库(2007?).它的列有:AnswerID(唯一的PK)、QuestionID(FK)(即唯一id(字段名)和Answer.QuestionID已经填充了唯一id(字段名).QuestionID有300多条记录.

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.

我需要做的是进入 XML 文件，找到 QuestionID(字段名称)，获取该字段名称的数据，然后将数据放入与该字段匹配的 DB 列Answer"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?

注意: 我尝试使用 DB 导入 XML 数据向导，但信息分解为无法管理的表数量.

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

推荐答案

您可以将 XML 分解为行和列，然后使用它来更新您的表.这是您可以做什么的一个小例子.

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 

结果:

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

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

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.

编辑

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.

这篇关于从 XML 更新 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！