CODEWITHSUNDEEP
c#sql-serverxmlt-sqlsql-server-2012
Osama AbuSitta
Osama AbuSitta

Reputation: 4066

Improve read XML string in sql server

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

Upvotes: 3

Views: 2205

Answers (2)

Osama AbuSitta
Osama AbuSitta

Reputation: 4066

Thanks @ughai I am use your way but I convert the 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)

Upvotes: 0

ughai
ughai

Reputation: 9880

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)

Upvotes: 3

Related Questions