OpenXML limitation - row based operation

I needed to insert a bulk of data into a table and designed to use OpenXML and there is a biz reqt to compute (Max+1) for the vdrNum column.

CREATE TABLE OpenXMLTest2
(
vdrNum INT NOT NULL,
vdrType CHAR(1) NOT NULL,
vdrName CHAR(10)
)
GO
ALTER TABLE OpenXMLTest2
ADD CONSTRAINT PK_OpenXMLTest2 PRIMARY KEY CLUSTERED
(
vdrNum,
vdrType
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
DECLARE @hDoc INT

EXEC sp_xml_preparedocument @hDoc OUTPUT,
'<ROOT><OpenXMLTest2 vdrName="Vend1" /><OpenXMLTest2 vdrName="Vend2" /></ROOT>'

INSERT INTO OpenXMLTest2 (vdrNum,vdrType,vdrName)
SELECT
(SELECT ISNULL(MAX(vdrNum),0)+1 FROM OpenXMLTest2 WHERE vdrType = 'x' )
,'x',vdrName
FROM OPENXML (&hDoc, 'ROOT/OpenXMLTest2',1) WITH (vdrName CHAR(10))

Looks good, but later found that this actually gives a primary key violation as vdrNum computed is always the same intial value and hence OpenXML doesn't work like what i expected. Workaround for the above code is


DECLARE @hDoc INT, @Val int

SELECT @Val = ISNULL(MAX(vdrNum),0)+1
FROM OpenXMLTest2 WHERE vdrType = 'x'

EXEC sp_xml_preparedocument @hDoc OUTPUT,
'<ROOT><OpenXMLTest2 vdrName="Vend1" vdrNum="1" /><OpenXMLTest2 vdrName="Vend2" vdrNum="2" /></ROOT>'

INSERT INTO OpenXMLTest2 (vdrNum,vdrType,vdrName)
SELECT vdrNum+1,'x',vdrName
FROM OPENXML (&hDoc, 'ROOT/OpenXMLTest2',1)
WITH (vdrName CHAR(10), vdrNum INT)

This reminds me an intresting problem, one of my coworker had with OpenXML, he had to delete some rows whose primary key columns values are available. Tricky part was, that table had a forignkey constraint that refered to itself (kind of hierarchical organization structure). Though he had the XML created in the correct order so that it doesn't make any foriegn key violation while deleting a row, it always throwed an foriegn key violation error. Later it was found that OpenXML doesn't actually didn't delete the rows as in the order in the XML and most probably spawned into multiple threads internal to SQLServer and hence tried to delete a parent row while its child row is still not deleted. Later when XML was dumped into a temp table and looped that to to delete in correct order as expected.