存储过程中的 TSQL 解析 XML

问题描述

我有 4 个包含各种数据的表.

I have 4 tables which contain various pieces of data.

表 1 包含问题表 2 包含组表 3 包含选项表 4 包含带有个人资料数据列的职位申请

Table 1 contains Questions Table 2 contains Groups Table 3 contains Options Table 4 contains job applications with a column for Profile Data

生成配置文件数据并转换为 XML 并存储在表 4 的 profileData 列中.其格式如下:

The profile data is generated and converted to XML and stored in the profileData column on table 4. Its format is as so:

<proficiencies>
<question>
  <questionID>2</questionID>
  <questionGroup>2</questionGroup>
  <questionOptions>
     <option>19</option>
     <option>20</option>
     <option>31</option>
  </questionOptions>
</question>
<question>
  <questionID>1</questionID>
  <questionGroup>1</questionGroup>
  <questionOptions>
     <option>20</option>
     <option>29</option>
     <option>21</option>
  </questionOptions>
 </question>
 <question>
  <questionID>3</questionID>
  <questionGroup>2</questionGroup>
  <questionOptions>
     <option>18</option>
     <option>29</option>
  </questionOptions>
 </question>
</proficiencies>

现在，我正在创建一个页面，我需要在其中使用所有选择的设置重新创建工作申请.创建 XML 时，我使用的是与实际问题和信息相关联的 ID 号.

Now, I am creating a page where I need to re-create the job application with all of the settings those chose. When I create the XML, I am using the ID numbers that are tied to the actual question and information.

现在找出这些 ID 号的最佳方法是什么?我认为最好在存储过程中执行此操作，因为如果我在 javascript 中解析它时执行此操作，它将有很多数据库调用.

What is the best way to be able to find out what those ID numbers are now? I figured it would be best to do this in the stored procedure because If i did it while parsing it in javascript, it would have a lot of database calls.

只是不确定如何最好地解决这个问题或更改我的数据库结构.

Just not sure how to best go about this or change the structure of my database.

在示例中，我需要找出问题 ID = 2 等的问题所在.

In the example, I need to find out what the question is where question ID = 2 etc.

有没有更好的方法来做到这一点?

Is there a better way to do this?

推荐答案

我认为您正在寻找 XQuery.

下面的示例解析您的 XML 并连接到一个问题表.它使用 nodes()、query() 和 value() 方法.

Example below parses out your XML and joins to a questions table. It uses the nodes(), query(), and value() methods.

with MyXML
as
(
select
1 as MyXMLDataID
,cast('
<proficiencies>
<question>
  <questionID>2</questionID>
  <questionGroup>2</questionGroup>
  <questionOptions>
     <option>19</option>
     <option>20</option>
     <option>31</option>
  </questionOptions>
</question>
<question>
  <questionID>1</questionID>
  <questionGroup>1</questionGroup>
  <questionOptions>
     <option>20</option>
     <option>29</option>
     <option>21</option>
  </questionOptions>
 </question>
 <question>
  <questionID>3</questionID>
  <questionGroup>2</questionGroup>
  <questionOptions>
     <option>18</option>
     <option>29</option>
  </questionOptions>
 </question>
</proficiencies>' as xml) as MyXMLData),
MyQuestionGroups
as
(
select 1 as questionGroup, 'Education' as questionGroup_description
union
select 2 as questionGroup, 'Experience' as questionGroup_description
),
MyQuestions
as
(
select 1 as questionGroup, 1 as questionID, 'High school attendance' as question
union
select 2 as questionGroup, 2 as questionID, 'Alchemy experience' as question
union
select 2 as questionGroup, 3 as questionID, 'Arbitrage experience' as question
),
MyOptions
as
(
select 18 as optionID, '1 year' as option_description
union
select 19 as optionID, '2 year' as option_description
union
select 20 as optionID, '3 years' as option_description
union
select 21 as optionID, '4 year' as option_description
union
select 29 as optionID, '5 year' as option_description
union
select 31 as optionID, '6 years' as option_description
)
SELECT MyXML.MyXMLDataID
      ,t1.questionID.query('.') as questionID_node
      ,q.question
  FROM   MyXML
 CROSS APPLY MyXMLData.nodes('/proficiencies/question/questionID') as t1(questionID)
 INNER JOIN MyQuestions q 
         on q.questionID = t1.questionID.value('.', 'int')

它返回这个结果:

-----------------------------------------------
MyXMLDataID questionID_node question
-----------------------------------------------
1   <questionID>2</questionID>  Alchemy experience
1   <questionID>1</questionID>  High school attendance
1   <questionID>3</questionID>  Arbitrage experience

这篇关于存储过程中的 TSQL 解析 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！