XML string to SQL table.

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

windows metro apps csharpvb

sql server mirroring

XML string to SQL table.

Category: sql server xml

Question

Replies