By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(4000);
SET @XmlDocument = N'<ROOT>
<projectId>15468</projectId><projectId>15469</projectId><projectId>15472</projectId>
<projectId>15849</projectId><projectId>13801</projectId><projectId>13802</projectId>
<projectId>13803</projectId><projectId>15684</projectId><projectId>14044</projectId>
<projectId>15722</projectId><projectId>15753</projectId><projectId>15770</projectId>
<projectId>15771</projectId>
</ROOT>';
BEGIN TRY
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, 'ROOT/projectId', 2)
WITH (projectId VARCHAR(10) 'text()');
EXEC sp_xml_removedocument @DocHandle;
END TRY
BEGIN CATCH
EXEC sp_xml_removedocument @DocHandle;
THROW;
END CATCH;
| projectId |
|---|
| 15468 |
| 15469 |
| 15472 |
| 15849 |
| 13801 |
| 13802 |
| 13803 |
| 15684 |
| 14044 |
| 15722 |
| 15753 |
| 15770 |
| 15771 |