The Best Way to shred XML data into SQL Server database columns -

what best way shred xml data various database columns? far have been using nodes , value functions so:

insert some_table (column1, column2, column3) select rows.n.value('(@column1)[1]', 'varchar(20)'), rows.n.value('(@column2)[1]', 'nvarchar(100)'), rows.n.value('(@column3)[1]', 'int'), @xml.nodes('//rows') rows(n) 

however find getting slow moderate size xml data.

stumbled across question whilst having similar problem, i'd been running query processing 7.5mb xml file (~approx 10,000 nodes) around 3.5~4 hours before giving up.

however, after little more research found having typed xml using schema , created xml index (i'd bulk inserted table) same query completed in ~ 0.04ms.

how's performance improvement!

code create schema:

if exists ( select * sys.xml_schema_collections [name] = 'myxmlschema') drop xml schema collection [myxmlschema] go  declare @myschema xml set @myschema =  (     select * openrowset     (         bulk 'c:\path\to\schema\myschema.xsd', single_clob      ) xmldata )  create xml schema collection [myxmlschema] @myschema  go 

code create table typed xml column:

create table [dbo].[xmlfiles] (     [id] [uniqueidentifier] not null,      -- data cv element      [data] xml(content dbo.[myxmlschema]) not null,  constraint [pk_xmlfiles] primary key nonclustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary] 

code create index

create primary xml index pxml_data on [dbo].[xmlfiles] (data) 

there few things bear in mind though. sql server's implementation of schema doesn't support xsd:include. means if have schema references other schema, you'll have copy of these single schema , add that.

also error:

xquery [dbo.xmlfiles.data.value()]: cannot implicitly atomize or apply 'fn:data()' complex content elements, found type 'xs:anytype' within inferred type 'element({http://www.mynamespace.fake/schemas}:sequencenumber,xs:anytype) ?'. 

if tried navigate above node had selected nodes function. e.g.

select     ,c.value('cvelementid[1]', 'int') [cvelementid]     ,c.value('../sequencenumber[1]', 'int') [level]      [dbo].[xmlfiles] cross apply     [data].nodes('/cvset/level/cvelement') t(c) 

found best way handle use outer apply in effect perform "outer join" on xml.

select     ,c.value('cvelementid[1]', 'int') [cvelementid]     ,b.value('sequencenumber[1]', 'int') [level]      [dbo].[xmlfiles] cross apply     [data].nodes('/cvset/level') t(b) outer apply     b.nodes ('cvelement') s(c) 

hope that helps that's pretty been day.