Best gaming deals on Amazon | This weeks Xbox deals with gold

XML string to SQL table.

Category: sql server xml

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

Latest posts in the category


sql server dataaccess

sql server sqlserver_ja