Improve read XML string in sql server

Question

I have the xml string sends 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 working fine, But the sp_xml_preparedocument is 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 I can get this Ids without change xml structure

Answer 1

You can use XML types and related XML methods .node / .value to achieve this.

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)