XML导入SqlServer，存储过程解析XML

.net代码（选代码摘自Nop）

public virtual void ImportResourcesFromXml(Language language, string xml)
        {
            if (language == null)
                throw new ArgumentNullException("language");

            if (String.IsNullOrEmpty(xml))
                return;
            if (_commonSettings.UseStoredProceduresIfSupported && _dataProvider.StoredProceduredSupported)
            {
                //SQL 2005 insists that your XML schema incoding be in UTF-16.
                //Otherwise, you'll get "XML parsing: line 1, character XXX, unable to switch the encoding"
                //so let's remove XML declaration
                var inDoc = new XmlDocument();
                inDoc.LoadXml(xml);
                var sb = new StringBuilder();
                using (var xWriter = XmlWriter.Create(sb, new XmlWriterSettings { OmitXmlDeclaration = true }))
                {
                    inDoc.Save(xWriter);
                    xWriter.Close();
                }
                var outDoc = new XmlDocument();
                outDoc.LoadXml(sb.ToString());
                xml = outDoc.OuterXml;

                //stored procedures are enabled and supported by the database.
                var pLanguageId = _dataProvider.GetParameter();
                pLanguageId.ParameterName = "LanguageId";
                pLanguageId.Value = language.Id;
                pLanguageId.DbType = DbType.Int32;

                var pXmlPackage = _dataProvider.GetParameter();
                pXmlPackage.ParameterName = "XmlPackage";
                pXmlPackage.Value = xml;
                pXmlPackage.DbType = DbType.Xml;

                //long-running query. specify timeout (600 seconds)
                _dbContext.ExecuteSqlCommand("EXEC [LanguagePackImport] @LanguageId, @XmlPackage", false, 600, pLanguageId, pXmlPackage);
            }
}

存储过程代码（摘自Nop）

ALTER PROCEDURE [dbo].[LanguagePackImport]
(
    @LanguageId int,
    @XmlPackage xml
)
AS
BEGIN
    IF EXISTS(SELECT * FROM [Language] WHERE [Id] = @LanguageId)
    BEGIN
        CREATE TABLE #LocaleStringResourceTmp
            (
                [LanguageId] [int] NOT NULL,
                [ResourceName] [nvarchar](200) NOT NULL,
                [ResourceValue] [nvarchar](MAX) NOT NULL
            )

        INSERT INTO #LocaleStringResourceTmp (LanguageID, ResourceName, ResourceValue)
        SELECT    @LanguageId, nref.value('@Name', 'nvarchar(200)'), nref.value('Value[1]', 'nvarchar(MAX)')
        FROM    @XmlPackage.nodes('//Language/LocaleResource') AS R(nref)

        DECLARE @ResourceName nvarchar(200)
        DECLARE @ResourceValue nvarchar(MAX)
        DECLARE cur_localeresource CURSOR FOR
        SELECT LanguageID, ResourceName, ResourceValue
        FROM #LocaleStringResourceTmp
        OPEN cur_localeresource
        FETCH NEXT FROM cur_localeresource INTO @LanguageId, @ResourceName, @ResourceValue
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (EXISTS (SELECT 1 FROM [LocaleStringResource] WHERE LanguageID=@LanguageId AND ResourceName=@ResourceName))
            BEGIN
                UPDATE [LocaleStringResource]
                SET [ResourceValue]=@ResourceValue
                WHERE LanguageID=@LanguageId AND ResourceName=@ResourceName
            END
            ELSE 
            BEGIN
                INSERT INTO [LocaleStringResource]
                (
                    [LanguageId],
                    [ResourceName],
                    [ResourceValue]
                )
                VALUES
                (
                    @LanguageId,
                    @ResourceName,
                    @ResourceValue
                )
            END
            
            
            FETCH NEXT FROM cur_localeresource INTO @LanguageId, @ResourceName, @ResourceValue
            END
        CLOSE cur_localeresource
        DEALLOCATE cur_localeresource

        DROP TABLE #LocaleStringResourceTmp
    END
END

具体的类型结构，关联方法及表结构都很简单，就不在此列出了。