问题
如何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_dataCAST()
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 将在您的表中创建一个新的数据集 - 根据您的要求
在其他列中 module_guid 不应包含 NULL ..您必须为此和所有其他非空列指定一个值...因此出现错误消息
当您想将这些 XML 数据添加到现有数据集时，我假设您最好使用更新语句。
例如