Serializing table data for SOAP posts in T-SQL

In an integration setup where a SQL Server trigger uses a CLR method with a generic WS client to publish changes to table data, you need to transform the 'inserted' table data to a serialized XML. In this example the CLR method simply wraps the xml content in the common SOAP envelope, and uses HttpWebRequest to post it to the specified  SOAP action and endpoint URL.

CREATE TRIGGER oncustomermodified  
    ON dbo.[CRONUS Danmark A_S$Customer]
    AFTER UPDATE AS 
BEGIN  
    DECLARE @url NVARCHAR(255) = N'http://testsrv/publish.svc';
    DECLARE @soapAction NVARCHAR(255);
    DECLARE @data XML;
    SET @soapAction = N'urn:test:ws/ITestEndPoint/OnCustomerUpdated';
    WITH xmlnamespaces (
        DEFAULT 'urn:test:ws', 
        'http://schemas.datacontract.org/2004/07/System' as dc)
    SELECT @data = (
        SELECT TOP 1 
            '' as 'dc:__identity', 
            [No_] AS 'ContactId', 
            [Name] AS 'FirstName' 
            FROM inserted 
            FOR xml path('entity'), root('OnCustomerUpdated') 
            );    
    EXEC dbo.PublishToWS @url, @soapAction, @data;
END  
GO

Note the __identity element is required for the WS endpoint to deserialize the record.

Google
m@kli.dk @klinkby RSS feed  GitHub