Pergunta
Como posso inserir CAST(event_data as XML)dados em uma coluna recém-adicionada em uma tabela temporária com a seguinte instrução:
INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(event_data AS XML) FROM #Unique_Logins
...sem receber a seguinte mensagem de erro:
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.
Passos para reproduzir
Estou tentando ler algum XML de um arquivo XEL importado. Ao fazer isso, tenho que criar uma nova coluna para converter os dados de nvarchar(max)para XML.
1. Vamos ver como podemos importar um arquivo de log de eventos estendido
sp_help fn_xe_file_target_read_file
Informações gerais
Name | Owner | Type | Created_datetime ----------------------------+---------+-----------------+------------------ fn_xe_file_target_read_file | sys | inline function | 2014-02-20 20:48:46.370
Colunas da função inline
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
Parâmetros da função inline
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. Insira os dados do arquivo XEL na tabela temporária #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)
Atenção:
importei um arquivo XEL de 2 GB que resultou em uma tabela temporária de 24 GB !
3. Selecione na tabela temporária para ver quais colunas temos
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
Agora os dados parecem ser XML. Vamos verificar se ele foi importado como XML.
4. Recupere os atributos da tabela temporária
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
Os dados XML na coluna event_dataforam importados para uma nvarchar(max)coluna.
5. Crie uma nova coluna event_data_xmlcomo XML e insira os dados da coluna event_datausandoCAST()
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
Mensagem de erro
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.
Não vejo o que module_guidtem a ver com a instrução INSERT/SELECT.
INSERT criará um novo conjunto de dados em sua tabela - conforme você exige que
entre outras colunas module_guid não deve conter NULL .. você tem que especificar um valor para esta e todas as outras colunas não nulas ... daí a mensagem de erro
Suponho que é melhor usar uma instrução de atualização quando quiser adicionar esses dados XML a um conjunto de dados existente.
por exemplo