使用openxml读取xml数据

xml的数据格式如下：

<?xml version="1.0"?>
<Language Name="Chinese">
  <LocaleResource Name="AboutUs">
    <Value>关于我们</Value>
  </LocaleResource>
  <LocaleResource Name="Account.AccountActivation">
    <Value>激活帐户</Value>
  </LocaleResource>
</Language>

sql的读取代码如下：

create table #T ( IntCol int, XmlCol xml );

insert  into #T ( XmlCol )
        select  *
        from    openrowset(bulk 'd:\pack3.3zhs.xml', single_blob) as x;
declare @hdoc int
declare @doc xml

select  @doc = XmlCol
from    #t

exec sp_xml_preparedocument @hdoc output, @doc
insert into LocalLanguage (languageID,LocaleResource,value)
select  3 as languageID,name as LocaleResource, value
from    openxml (@hdoc, '/Language/LocaleResource',2)
with  (Value varchar(500),Name varchar(500)'@Name') 
exec sp_xml_removedocument @hdoc

 

 

转载于:https://www.cnblogs.com/qiufuwu618/p/3803724.html