Search code examples
sqltriggerssql-server-2012ddlaudit

Sql server 2012 Trigger: Convert row to xml and write to column error

I am having an issue writing a trigger which is supposed to convert a row on a table to XML and writing it to a single column in another table. The Whenever a new row is inserted into this table (person.address) I want this trigger to send the row (converted to XML) to another table (audit.table) Here is my Trigger. 

       Create trigger [Person].[sPerson] ON [Person].[Address_s] for INSERT AS
begin
    DECLARE @xml XML;
    SET @xml =  
    (
      SELECT [AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [SpatialLocation.ToString()], [PostalCode], [rowguid], [ModifiedDate]

      FROM INSERTED
      FOR XML PATH('Inserted')
    );
    insert into AdventureWorks2012.HumanResources.Audit.Table(AuditXml) VALUES (@xml);
end

My error is

FOR XML does not support CLR types - cast CLR types explicitly into one of the supported types in FOR XML queries.

I have a column on the audit.table called AuditXml of type XML. It seems to think AuditXML is invalid. Thank you.

Solution

  • It is the column SpatialLocation in Person.Address that makes your for xml query to fail.

    SpatialLocation is of type geography and that is a CLR type.