0

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.

3

INSERT will create a new data-set in your table - as you demand that

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)

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.

UPDATE #Unique_Logins SET event_data_xml = CAST(event_data AS XML)
  • True - but I suppose the guid-columns are references to modules-table and package-table .. and his existing data show that they exist ... -> update – eagle275 Nov 13 '19 at 11:11
  • 1
    So this must be one of those face-palm moments. I didn't see the obvious. Thank you for pointing that out. – John aka hot2use Nov 13 '19 at 11:54
  • 1
    I can confirm that running UPDATE #Unique_Logins SET event_data_xml = CAST(event_data AS XML) returns as Command(s) completed successfully. Thanks again for pointing out the obvious. – John aka hot2use Nov 13 '19 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.