Import from xml to identity column-CodePudding

I tried import some data from XML into a temporary table.

My code:

DECLARE @xmlData XML

SET @xmlData = '
<PersonsInfo>
  <Person BussEntityId="1">
    <Name>
      <First>Ken</First>
      <Middle>J</Middle>
      <Last>Sánchez</Last>
    </Name>
  </Person>
  <Person BussEntityId="2">
    <Name>
      <First>Terri</First>
      <Middle>Lee</Middle>
      <Last>Duffy</Last>
    </Name>
  </Person>
</PersonsInfo>'

CREATE TABLE #Person 
(
    PersonId int IDENTITY PRIMARY KEY,
    FirstName varchar(20),
    MiddleName varchar(20),
    LastName varchar(20)
)

SET IDENTITY_INSERT #Person ON;

INSERT INTO #Person
    SELECT
        T.e.value('@BussEntityId', 'int') AS PersonId,
        P.elem.value('First[1]', 'varchar(20)') AS FirstName,  
        P.elem.value('Middle[1]', 'varchar(20)') AS MiddleName,
        P.elem.value('Last[1]', 'varchar(20)') AS LastName
    FROM 
        @xmlData.nodes('/PersonsInfo/Person') AS T(e)
    OUTER APPLY 
        T.e.nodes('Name') AS P(elem)

SET IDENTITY_INSERT #Person OFF

SELECT * FROM #Person

DROP TABLE #Person

I get an error:

An explicit value for the identity column in table '#Person' can only be specified when a column list is used and IDENTITY_INSERT is ON

I set IDENTITY_INSERT ON - so why is there a problem?

CodePudding user response：

As @DanGuzman already pointed out, there is a need to specify columns list explicitly for the INSERT INTO ... clause.

I also adjusted the XPath expressions for performance reasons.

SQL

USE tempdb;
GO

DECLARE @xmlData XML

SET @xmlData = '
<PersonsInfo>
  <Person BussEntityId="1">
    <Name>
      <First>Ken</First>
      <Middle>J</Middle>
      <Last>Sánchez</Last>
    </Name>
  </Person>
  <Person BussEntityId="2">
    <Name>
      <First>Terri</First>
      <Middle>Lee</Middle>
      <Last>Duffy</Last>
    </Name>
  </Person>
</PersonsInfo>'

CREATE TABLE #Person (
    PersonId int identity primary key 
    ,FirstName varchar(20)
    ,MiddleName varchar(20)
    ,LastName varchar(20)
    )

SET IDENTITY_INSERT #Person ON;

INSERT INTO #Person (PersonId, FirstName, MiddleName, LastName)
SELECT e.value('@BussEntityId', 'int') AS PersonId
    ,elem.value('(First/text())[1]', 'varchar(20)') AS FirstName
    ,elem.value('(Middle/text())[1]', 'varchar(20)') AS MiddleName
    ,elem.value('(Last/text())[1]', 'varchar(20)') AS LastName
FROM @xmlData.nodes('/PersonsInfo/Person') AS T(e)
    OUTER APPLY T.e.nodes('Name') AS P(elem);

SET IDENTITY_INSERT #Person OFF;

SELECT * FROM #Person;

DROP TABLE #Person;

Output

 ---------- ----------- ------------ ---------- 
| PersonId | FirstName | MiddleName | LastName |
 ---------- ----------- ------------ ---------- 
|        1 | Ken       | J          | Sánchez  |
|        2 | Terri     | Lee        | Duffy    |
 ---------- ----------- ------------ ----------