In SQL Server, can I replace multiple inserts with a single insert that accepts an XML parameter?

Question

So, I have an existing ASP.NET solution that uses LINQ-to-SQL to insert data into SQL Server (5 tables, total 110 thousand records). In the past, I read that XML can be passed as a parameter to SQL Server, but my Google searches show results that store XML directly in a table. I would rather take this XML parameter and insert the nodes as records.

Is it possible? How is this done (for example, how is an XML parameter used to insert records in T-SQL, how should XML be formatted)?

Note. I am exploring other options, such as SQL bulk copy, and I know that SSIS would be a good alternative. I want to know if this XML approach is possible.

Answer 1

XML must be formatted as a regular XML document. Then you just pass it to the stored procedure using the XML parameter type.

Here is an example of how to make an insert. In this case, @p_AdditionalContactInfo is XML, and it is in this form:

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

Here is the t-sql example:

  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

Answer 2

For SQL Server 2008, there is no longer any need to use outdated methods, such as using FOR OPENXML - this is a pretty beast of an interface and not very useful.

Instead, use the built-in SQL Server XQuery to “drop” your XML document into parts and save them:

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)

Answer 3

, XML SQL Server. sp_xml_preparedocument ..

I wrote an article about the various approaches that you could take to pass a recordset to sproc so that they retain the db parameter (CSV vs XML vs TABLE value) - take a look at this, as I provided examples of approaches and performed a performance comparison. In short, I would recommend checking Tabular Parameters - this is a much more natural way to do this and give better performance.