TSQL XML Parsing in a Stored Procedure

Question

I have 4 tables that contain various pieces of data.

Table 1 contains questions; Table 2 contains groups; Table 3 contains parameters; Table 4 contains applications for working with a column for profile data

Profile data is generated and converted to XML and stored in the profileData column of table 4. Its format is as follows:

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

Now I am creating a page where I need to recreate the job application with all the settings selected. When I create XML, I use ID numbers that are tied to a relevant issue and information.

What is the best way to find out what ID numbers are? I decided that it would be better to do this in a stored procedure, because if I did this by parsing it in javascript, it would have many calls in the database.

, .

, : ID = 2 ..

?

Answer 1

, XQuery.

XML . (), query() ().

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