Question
How can I insert CAST(event_data as XML) data into a newly added column in a temporary table with the following statement:
INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(event_data AS XML) FROM #Unique_Logins
…without receiving the following error message:
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.
Steps to Reproduce
I am trying to read some XML from an imported XEL file. In doing so I have to create a new column to convert the data from nvarchar(max) to XML.
1. Let's see how we can import an extended event log file
sp_help fn_xe_file_target_read_file
General Info
Name | Owner | Type | Created_datetime ----------------------------+---------+-----------------+------------------ fn_xe_file_target_read_file | sys | inline function | 2014-02-20 20:48:46.370
Columns of inline function
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
Parameters of inline function
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. Insert data from XEL file into temporary table #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)
Caution:
I imported a 2 GB XEL file which resulted in a 24 GB temporary table!
3. Select from temporary table to see what columns we have
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
Now the data seems to be XML. Let's verify that it has been imported as XML.
4. Retrieve temporary table attributes
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
The XML data in the column event_data has been imported to a nvarchar(max) column.
5. Create new column event_data_xml as XML and insert column data from event_data using 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
Error Message
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.
I don't see what module_guid has to do with the INSERT / SELECT statement.
Best Answer
INSERT will create a new data-set in your table - as you demand that
among other columns module_guid shall not contain NULL .. you have to specify a value for this and all other not null columns ... hence the error message
I assume you better use an update statement, when you want to add those XML-data to an existing data-set.
e.g.