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

Saving your XML to an XML-typed column instead of an NVARCHAR column would be a good start. — Tomalak
– Tomalak, Commented Jun 8, 2015 at 6:57
Thanks @Tomalak , but I have a big project working on this approach — Osama AbuSitta
– Osama AbuSitta, Commented Jun 8, 2015 at 7:03
Then I can only suggest that you wait for that project to finish. Working with XML efficiently requires XML-typed columns. — Tomalak
– Tomalak, Commented Jun 8, 2015 at 7:05

ughai · Accepted Answer · 2015-06-08 07:06:21Z

3

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)

answered Jun 8, 2015 at 7:06

ughai

9,9004 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Osama AbuSitta · Accepted Answer · 2015-06-09 05:53:19Z

0

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)

answered Jun 9, 2015 at 5:53

Osama AbuSitta

4,0964 gold badges40 silver badges56 bronze badges

Collectives™ on Stack Overflow

Improve read XML string in sql server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related