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

Question

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>

What programming language are you using? What have you tried to parse the data from XML? — Bhavik Shah
– Bhavik Shah, Commented Jun 19, 2017 at 5:34
This is the format that i will get the file in need to read it and store it in the table — Gurdeep Suri
– Gurdeep Suri, Commented Jun 19, 2017 at 5:36
That isn't proper XML unless you copy pasted it incorrectly without quotation marks. — ZLK
– ZLK, Commented Jun 19, 2017 at 5:38

Pரதீப் · Accepted Answer · 2017-06-19 05:41:56Z

1

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

answered Jun 19, 2017 at 5:41

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gurdeep Suri Over a year ago

Thanks your output helped.Got what i was looking for.Actually the xml was provided by the developer and i had to read and insert it into the table.So asked the developer to make the changes accordingly

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related