Clever Geek Handbook

Improve reading XML string in SQL Server

I have xml string being sent to SP as nvarchar (Max)

'<Devices><ID value="13" /><ID value="39" /></Devices>'

And I use this way to return ids

DECLARE @DeviceIDs nvarchar(max) = N'<Devices><ID value="13" /><ID value="39" /></Devices>'
       ,@iDevice INT;
DECLARE @Devices table (DeviceId int PRIMARY KEY)
                EXEC sp_xml_preparedocument @iDevice OUTPUT, @DeviceIDs
                Insert Into @Devices(DeviceId)
                SELECT value FROM OPENXML (@iDevice, '/Devices/ID',3) WITH (value int)
                EXEC sp_xml_removedocument @iDevice 

SELECT * FROM @Devices

The previous code works fine, but sp_xml_preparedocument is an extended stored procedure and according to technet.microsoft.com: this feature will be removed in a future version of Microsoft SQL Server Extended Stored Procedures

How can I get these IDs without modifying the xml structure

+3


source to share


2 answers


You can use XML / XML

types and related methods to achieve this..node

.value 



DECLARE @DeviceIDs XML = N'<Devices><ID value="13" /><ID value="39" /></Devices>'
SELECT  c.value('@value','int') as DeviceID
FROM @DeviceIDs.nodes('Devices/ID') as t(c)
+3


source


Thanks @ughai I am using my path but I am converting nvarchar to xml



DECLARE @DeviceIDsStr nvarchar(max) = N'<Devices><ID value="13" /><ID value="39" /></Devices>';
DECLARE @DeviceIDs XML = CAST(@DeviceIDsStr AS XML) ;
SELECT  c.value('@value','int') as DeviceID
FROM @DeviceIDs.nodes('Devices/ID') as t(c)
0


source






More articles:


All Articles