無法將轉換的 XML 數據插入到臨時表的新列中

問題

如何CAST(event_data as XML)使用以下語句將數據插入到臨時表中新添加的列中：

INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(event_data AS XML) FROM #Unique_Logins

…沒有收到以下錯誤消息：

Msg 515, Level 16, State 2, Line 36
Cannot insert the value NULL into column 'module_guid', table 'tempdb.dbo.#Unique_Logins______________________________________________________________________________________________________00000000000F'; column does not allow nulls. INSERT fails.
The statement has been terminated.

重現步驟

我正在嘗試從導入的 XEL 文件中讀取一些 XML。在這樣做時，我必須創建一個新列來將數據nvarchar(max)從XML.

1. 讓我們看看如何導入擴展的事件日誌文件

---

sp_help fn_xe_file_target_read_file

基本資訊

Name                        | Owner   | Type            | Created_datetime
----------------------------+---------+-----------------+------------------
fn_xe_file_target_read_file | sys     | inline function | 2014-02-20 20:48:46.370

內聯函式的列

Column_name  | Type             | Computed | Length | Prec  | Scale | Nullable | TrimTrailingBlanks | FixedLenNullInSource | Collation
-------------+------------------+----------+--------+-------+-------+----------+--------------------+----------------------+------------------------------
module_guid  | uniqueidentifier | no       | 16     |       |       | no       | (n/a)              | (n/a)                | NULL
package_guid | uniqueidentifier | no       | 16     |       |       | no       | (n/a)              | (n/a)                | NULL
object_name  | nvarchar         | no       | 120    |       |       | no       | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
event_data   | nvarchar         | no       | -1     |       |       | yes      | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
file_name    | nvarchar         | no       | 520    |       |       | no       | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
file_offset  | bigint           | no       | 8      | 19    | 0     | no       | (n/a)              | (n/a)                | NULL

內聯函式的參數

Parameter_name     | Type     | Length | Prec | Scale | Param_order | Collation
-------------------+----------+--------+------+-------+-------------+----------------
@path              | nvarchar | 520    | 260  | NULL  | 1           | Latin1_General_CS_AS
@mdpath            | nvarchar | 520    | 260  | NULL  | 2           | Latin1_General_CS_AS
@initial_file_name | nvarchar | 520    | 260  | NULL  | 3           | Latin1_General_CS_AS
@initial_offset    | bigint   | 8      | 19   | 0     | 4           | NULL

2. 將 XEL 文件中的數據插入臨時表 #Unique_Logins

---

select * into #Unique_Logins from sys.fn_xe_file_target_read_file(N'C:\temp\Unique_Logins_0_132175196428210000.xel',NULL,NULL,NULL)

注意：

我導入了一個2 GB XEL 文件，生成了一個24 GB 臨時表！

3. 從臨時表中選擇，看看我們有哪些列

---

select TOP 2 * from #Unique_Logins

module_guid                          | package_guid                         | object_name | event_data                                                                                                                                                                                                                                                                                            | file_name                                      | file_offset
-------------------------------------+--------------------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+------------
CE79811F-1A80-40E1-8F5D-7445A3F375E7 | 655FD93F-3364-40D5-B2BA-330F7FFB6491 | login       | <event name="login" package="sqlserver" timestamp="2019-11-06T13:14:02.835Z">...<action name="database_name" package="sqlserver"><value><![CDATA[DatabaseName1]]></value></action>...<action name="server_principal_name" package="sqlserver"><value><![CDATA[DB_USER1]]></value></action>...</event> | C:\temp\Unique_Logins_0_132175196428210000.xel | 6656
CE79811F-1A80-40E1-8F5D-7445A3F375E7 | 655FD93F-3364-40D5-B2BA-330F7FFB6491 | login       | <event name="login" package="sqlserver" timestamp="2019-11-06T13:14:02.894Z">...<action name="database_name" package="sqlserver"><value><![CDATA[DatabaseName2]]></value></action>...<action name="server_principal_name" package="sqlserver"><value><![CDATA[DB_USER2]]></value></action>...</event> | C:\temp\Unique_Logins_0_132175196428210000.xel | 6656

現在數據似乎是 XML。讓我們驗證它是否已作為 XML 導入。

4.檢索臨時表屬性

USE TEMPDB --caution; temporary table is in tempdb
GO
SELECT sc.name, 
      st.name, 
      st.max_length, 
      st.collation_name, 
      sc.column_id, 
      sc.collation_name, 
      sc.max_length, 
      sc.is_nullable, 
      sc.is_xml_document
FROM sys.columns AS sc JOIN sys.types AS st 
   ON sc.user_type_id = st.user_type_id WHERE sc.object_id = OBJECT_ID('#Unique_Logins')

name         | name             | max_length | collation_name       | column_id | collation_name       | max_length | is_nullable | is_xml_document
-------------+------------------+------------+----------------------+-----------+----------------------+------------+-------------+----------------
module_guid  | uniqueidentifier | 16         | NULL                 | 1         | NULL                 | 16         | 0           | 0
package_guid | uniqueidentifier | 16         | NULL                 | 2         | NULL                 | 16         | 0           | 0
object_name  | nvarchar         | 8000       | Latin1_General_CS_AS | 3         | Latin1_General_CS_AS | 120        | 0           | 0
event_data   | nvarchar         | 8000       | Latin1_General_CS_AS | 4         | Latin1_General_CS_AS | -1         | 1           | 0
file_name    | nvarchar         | 8000       | Latin1_General_CS_AS | 5         | Latin1_General_CS_AS | 520        | 0           | 0
file_offset  | bigint           | 8          | NULL                 | 6         | NULL                 | 8          | 0           | 0

列中的 XML 數據event_data已導入到nvarchar(max)列中。

5. 創建新列作為 XML 並從使用event_data_xml插入列數據event_data``CAST()

---

ALTER TABLE #Unique_Logins ADD event_data_xml XML
GO
INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(event_data AS XML) FROM #Unique_Logins

錯誤資訊

Msg 515, Level 16, State 2, Line 36
Cannot insert the value NULL into column 'module_guid', table 'tempdb.dbo.#Unique_Logins______________________________________________________________________________________________________00000000000F'; column does not allow nulls. INSERT fails.
The statement has been terminated.

我看不出module_guid與 INSERT / SELECT 語句有什麼關係。

INSERT 將在您的表中創建一個新的數據集 - 根據您的要求

Column_name  | Type             | Computed | Length | Prec  | Scale | Nullable | TrimTrailingBlanks | FixedLenNullInSource | Collation
-------------+------------------+----------+--------+-------+-------+----------+--------------------+----------------------+------------------------------
module_guid  | uniqueidentifier | no       | 16     |       |       | no       | (n/a)              | (n/a)                | NULL
package_guid | uniqueidentifier | no       | 16     |       |       | no       | (n/a)              | (n/a)                | NULL
object_name  | nvarchar         | no       | 120    |       |       | no       | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
event_data   | nvarchar         | no       | -1     |       |       | yes      | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
file_name    | nvarchar         | no       | 520    |       |       | no       | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
file_offset  | bigint           | no       | 8      | 19    | 0     | no       | (n/a)

在其他列中 module_guid 不應包含 NULL ..您必須為此和所有其他非空列指定一個值…因此出現錯誤消息

當您想將這些 XML 數據添加到現有數據集時，我假設您最好使用更新語句。

例如

UPDATE #Unique_Logins SET event_data_xml = CAST(event_data AS XML)

引用自：https://dba.stackexchange.com/questions/253157