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