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