DECLARE @xmlText xml ,@idoc int

set @xmlText = (select doc from openrowset(bulk 'C:\Word\SRC\WebApp\Demo\XML\XMLFile.xml',single_blob) as t(doc))

--select @xmlText

EXEC sp_xml_preparedocument @idoc OUTPUT,@xmlText

--insert into TB_TB_Category

-- (Cid, Name, ParentCid)

--select [cid],[name],[parent_cid]

select *

from openxml(@Idoc,'/itemcats_get_response/item_cats/item_cat',2)

With(

[cid] bigint,

[Cis_parent] nvarchar(100),

[name] nvarchar(50),

[parent_cid] bigint

)

--where cid='50000671'

EXEC sp_xml_removedocument @idoc