I'm currently using OPENXML to shred a large @xml variable. It can INSERT/SELECT thousands of shredded rows in a second. OPENXML is not scalable and it is deprecated. Therefore, I'm trying to switch to shredding using the new recommended way with .nodes and .value and it shreds in a second. But if I switch from SELECT to INSERT/SELECT it takes 30 seconds. To me that is irrational. Am I doing something wrong with the new method?
-- execute this SELECT to generate some xml
-- click on the xml to open it in a new window
-- copy/paste the xml into the "set @xml" statement below.
select top (3000) name, create_date
for xml path
declare @xml xml;
-- copy the generated xml into this statement:
set @xml =
declare @t table (name varchar(128) not null, create_date datetime not null);
--insert into @t -- uncomment this line and it takes 30 seconds. Why?
x.data.value('name','varchar(128)') as name,
x.data.value('create_date','datetime') as create_date