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, Jun 19 '17 at 05:34
This is the format that i will get the file in need to read it and store it in the table — Gurdeep Suri, Jun 19 '17 at 05:36
Check [this](https://stackoverflow.com/questions/3600091/how-to-pass-xml-from-c-sharp-to-a-stored-procedure-in-sql-server-2008?rq=1) — Bhavik Shah, Jun 19 '17 at 05:37
That isn't proper XML unless you copy pasted it incorrectly without quotation marks. — ZLK, Jun 19 '17 at 05:38
@ZLK that is the format that the C# developer has given me that the file would be in — Gurdeep Suri, Jun 19 '17 at 05:39

score 1 · Accepted Answer · answered Jun 19 '17 at 05:41

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

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 — Gurdeep Suri, Jun 19 '17 at 06:05

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

1 Answers1