CODEWITHSUNDEEP
sql-serverxml
keval balar
keval balar

Reputation: 50

How to read xml in SQL Server?

I'm currently working with an SQL Server 2012 that requires me to read XML string that can be read and insert data in the table using Stored Procedure.

DECLARE @TurnoverList VARCHAR(Max) = "<root><data><Value>1</Value><Value>3</Value><Value>2</Value><Value>4</Value></data></root>"
DECLARE @hDocTurnover INT

EXEC Sp_Xml_prepareDocument @hDocTurnover OUTPUT
    ,@TurnoverList

SELECT *
INTO #TempTurnover
FROM OpenXml(@hDocTurnover, 'root/data', 2) WITH (Value INT)

EXEC Sp_Xml_RemoveDocument @hDocTurnover

SELECT *
FROM #TempTurnover

But it Currently only one record 

Value 
------
1

Upvotes: 0

Views: 108

Answers (1)

Thom A
Thom A

Reputation: 95554

If you're simply after the value of each Value node, you'll be better off with:

--Note use of the CORRECT datatype. Use the xml datatype for XML, for a (n)varchar
DECLARE @XML xml = '<root><data><Value>1</Value><Value>3</Value><Value>2</Value><Value>4</Value></data></root>';

SELECT d.V.value('(./text())[1]','int') AS [Value]
FROM @XML.nodes('/root/data/Value') d(V);

DB<>Fiddle

Upvotes: 1

Related Questions