我嘗試將一些資料從 XML 匯入到臨時表中。
我的代碼：
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
我收到一個錯誤：
表 '#Person' 中標識列的顯式值只能在使用列串列且 IDENTITY_INSERT 為 ON 時指定
我設定了IDENTITY_INSERT ON- 那么為什么會出現問題？
uj5u.com熱心網友回復：
正如@DanGuzman 已經指出的那樣，需要為INSERT INTO ...子句明確指定列串列。
出于性能原因，我還調整了 XPath 運算式。
查詢陳述句
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;
輸出
---------- ----------- ------------ ----------
| PersonId | FirstName | MiddleName | LastName |
---------- ----------- ------------ ----------
| 1 | Ken | J | Sánchez |
| 2 | Terri | Lee | Duffy |
---------- ----------- ------------ ----------
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/366942.html
標籤：sql-server xml 身份