T-SQL - OPENXML

I have the following SQL code:

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>';
-- 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//', 2)
  WITH (projectId  VARCHAR(10));

If I run it like this, I only get project id into the first row, the rest are nulls. Any idea to specify the path correctly?

Thanks

Update. Actual code with nodes:

    SET NOCOUNT ON;

    create table #selectedProjectsList (projectId int)
    create index #idx_selectedProjectsList On #selectedProjectsList 
    (projectId)

    if @params is not null
    BEGIN
        if @params.value('count(//projectId)', 'int') > 0
        BEGIN
          insert into #selectedProjectsList
          select PIDS.PID.value('.', 'int')
          From @params.nodes('//projectId') as PIDS(PID)
        END
    END

params is a parameter that looks like this:

@params xml = null

Solution

Your primary issue was that you had the XPath wrong, it should have been ROOT/projectId. You then need to change your WITH to WITH (projectId VARCHAR(10) 'text()');. And for defensive coding you need to make sure to release the handle.

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;

db<>fiddle

But it's much better to use the newer (from 2005!) XML functions, such as .nodes and .value. Note that they only work on xml typed variables.

DECLARE @XmlDocument xml = 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>';

SELECT x1.prj.value('text()[1]', 'varchar(10)')
FROM @XmlDocument.nodes('ROOT/projectId') x1(prj);

db<>fiddle