在SQL Server中,是否可以将多个插入替换为一个接受XML参数的插入?

XML应该像普通XML文档那样格式化。然后使用参数类型XML将其传递给存储过程。

<entities>
  <entity>
    <firstName>Joey</firstName>
    ...        
  </entity>
  .. more entity records
</entities>

下面是t-sql示例:

  DECLARE @l_index int

  -- parse the records from the XML
  EXECUTE sp_xml_preparedocument @l_index OUTPUT, @p_AdditionalContactInfo
  INSERT INTO @l_AdditionalContactInfoTbl
            ( ContactInfoID
            , FirstName
            , LastName 
            , ContactTypeID
            , Title
            , Email
            , AddressLine1
            , AddressLine2
            , City
            , State
            , Zip
            , MobilePhone
            , BusinessPhone
            , UpdateDateTime )
       SELECT ContactInfoID
            , FirstName
            , LastName
            , ContactTypeID
            , Title
            , Email
            , AddressLine1
            , AddressLine2
            , City
            , State
            , Zip
            , MobilePhone
            , BusinessPhone
            , UpdateDateTime
         FROM OPENXML (@l_index, 'entities/entity', 1)
              WITH (  ContactInfoID  int          'id'
                    , FirstName      varchar(50)  'firstName'
                    , LastName       varchar(50)  'lastName'
                    , ContactTypeID  int          'contactTypeId'
                    , Title          varchar(20)  'title'
                    , Email          varchar(100) 'email'
                    , AddressLine1   varchar(100) 'addressLine1'
                    , AddressLine2   varchar(100) 'addressLine2'
                    , City           varchar(50)  'city'
                    , State          varchar(2)   'state'
                    , Zip            varchar(5)   'zip'
                    , MobilePhone    varchar(12)  'mobilePhone'
                    , BusinessPhone  varchar(12)  'businessPhone'
                    , UpdateDateTime datetime     'updateDateTime'
                   )
  EXECUTE sp_xml_removedocument @l_index

对于SQLServer2008,真的不再需要像用于OPENXML这样的遗留方法了——这是一个非常庞大的接口,而且不是很有用。

相反,请使用本机SQL Server XQuery将XML文档“切碎”为多个片段并存储这些片段:

INSERT INTO 
   dbo.YourTableName(.... list of fields.......)
   SELECT
       nodes.entity.value('(firstName)[1]', 'varchar(50)'),
       nodes.entity.value('(lastName)[1]', 'varchar(50)'),
        ........ (more columns as needed) ...........
   FROM
       @XmlInput.nodes('/entities/entity') AS nodes(entity)

是的,您可以利用SQLServer的内置XML支持。实际上,您不需要使用sp\uxml\upreparedocument等。

I wrote up an article 关于将一组记录传递到存储过程并持久化到db(CSV vs XML vs TABLE valued parameter)的各种方法,请看一下,因为我已经给出了这些方法的示例,并对它们进行了性能比较。简言之,我建议检查表值参数—这是一种更自然的方法,并提供了最佳性能。