Question
venkatflower87 on Mon, 14 Apr 2014 14:21:36
Hi All,
I am getting XML string from UI, I need load the XML string to three different tables. Please help me on this.
Xml string Input string :
<root>
<LevelList>
<Task_Number>6054</Task_Number>
<level_short_desc>UPC</level_short_desc>
<scope>Common</scope>
<group_id>0</group_id>
<expand_sequence>0</expand_sequence>
<prompt_level>0</prompt_level>
<sequence_number>9</sequence_number>
<prompt_type>VAR</prompt_type>
<prompt_id>6</prompt_id>
<description>Units High</description>
<Prompt>
<name>venkat</name>
<value>10</value>
<Question>
<Des1>What?</Des1>
<Des>Answer</Des>
</Question>
</Prompt>
<answer_type>SNG</answer_type>
<answer_length>0</answer_length>
</LevelList>
</root>
Expected SQL tables output :
Table Name (1) : Task
| Task_Number | level_short_desc | scope | group_id | expand_sequence | prompt_level | sequence_number | prompt_type | prompt_id | description | answer_type | answer_length |
| 6054 | upc | common | 0 | 0 | 0 | 0 | var | 6 | units high | sng | 0 |
Table Name (2) :Prompt
| Task_Number | Name | Value |
| 6054 | venkat | 10 |
Table name (3):Question
| Task_Number | Des1 | Des |
| 6054 | What? | Answer |
Can you please help me. For your reference I've attached input and output image.
Input ::
Output
Thanks
Bala
Replies
Olaf Helper on Mon, 14 Apr 2014 14:36:25
Hello,
Here a Little example where you can start with:
DECLARE @xml XML;
SET @xml =
N'<root>
<LevelList>
<Task_Number>6054</Task_Number>
<level_short_desc>UPC</level_short_desc>
<scope>Common</scope>
<group_id>0</group_id>
<expand_sequence>0</expand_sequence>
<prompt_level>0</prompt_level>
<sequence_number>9</sequence_number>
<prompt_type>VAR</prompt_type>
<prompt_id>6</prompt_id>
<description>Units High</description>
<Prompt>
<name>venkat</name>
<value>10</value>
<Question>
<Des1>What?</Des1>
<Des>Answer</Des>
</Question>
</Prompt>
<answer_type>SNG</answer_type>
<answer_length>0</answer_length>
</LevelList>
</root>';
SELECT nodes.Rows.value('Task_Number[1]', 'int') AS Task_Number
,nodes.Rows.value('level_short_desc[1]', 'varchar(30)') AS level_short_desc
FROM @xml.nodes('root/LevelList') AS nodes(Rows)
Prashanth Jayaram on Mon, 14 Apr 2014 14:42:00
you can refer the below link
http://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/
-Prashanth