CODEWITHSUNDEEP
c#sqlsql-serverxml
Gurdeep Suri
Gurdeep Suri

Reputation: 13

How to read xml file in stored procedure and insert it in table in sql server

Hi i have the Following XML file.how do i read it and insert the data in a table using a stored procedure 

<NewDataSet>
<Root RowNumber=1; answer = 1; TAnswer=null/>
<Root RowNumber=2; answer = 6; TAnswer=yes for Q 2/>
<Root RowNumber=3; answer = 9; TAnswer=null/>
<Root RowNumber=4; answer = -1; TAnswer=q 4 no suggestions/>
</NewDataSet>

Upvotes: 0

Views: 1359

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Considering you have a valid xml just like the one below. 

DECLARE @xml XML

SET @xml = '
<NewDataSet>
<Root RowNumber = "1" answer = "1" TAnswer = "null" />
<Root RowNumber = "2" answer = "6" TAnswer = "yes for Q 2" />
<Root RowNumber = "3" answer = "9" TAnswer = "null" />
<Root RowNumber = "4" answer = "-1" TAnswer = "q 4 no suggestions" />
</NewDataSet>'

SELECT RowNumber = T.A.value('@RowNumber', 'int'),
       answer = T.A.value('@answer', 'int'),
       TAnswer = T.A.value('@TAnswer', 'varchar(1000)')
FROM   @xml.nodes('//NewDataSet/Root') T (A)

Note : There are two mistakes in your XML. Attributes values are not enclosed by double quotes. Then the attributes should be separated by space not by semi-colon

Upvotes: 1

Related Questions