{"id":1922,"date":"2014-12-30T00:00:00","date_gmt":"2014-12-30T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/archiving-hierarchical-deleted-transactions-using-xml\/"},"modified":"2021-09-29T16:21:32","modified_gmt":"2021-09-29T16:21:32","slug":"archiving-hierarchical-deleted-transactions-using-xml","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/archiving-hierarchical-deleted-transactions-using-xml\/","title":{"rendered":"Archiving Hierarchical, Deleted Transactions Using XML"},"content":{"rendered":"
\n

\tAlthough I’m not generally a big proponent of storing XML in a SQL database, I feel that the archiving of deleted data that relates to a single business transaction is one of the rare cases where it is reasonable option. <\/p>\n

Some Sample Data<\/h2>\n

\tLet’s start by using Data Definition Language<\/a> (DDL) to create a couple of example tables, and use Data Manipulation Language<\/a> (DMS) to populate those tables. These are borrowed from a previous article (I love reusing code). The data model is an Invoices<\/strong> table with child, detail records stored in an Invoice_Details<\/strong> table. <\/p>\n

CREATE TABLE dbo.Invoices\n(\n    InvoiceNo           VARCHAR(20)     NOT NULL\n    ,InvoiceDate        DATE            NOT NULL\n    ,CustomerID         VARCHAR(20)     NOT NULL\n    ,CustomerName       VARCHAR(100)    NOT NULL\n    ,CustomerAddr1      VARCHAR(100)    NOT NULL\n    ,CustomerAddr2      VARCHAR(100)    NULL\n    ,CustomerZipcode    VARCHAR(10)     NOT NULL\n    ,PRIMARY KEY (InvoiceNo)\n);\n\n-- Seed the Invoices table\nINSERT INTO dbo.Invoices \n(\n    InvoiceNo, InvoiceDate, CustomerID, CustomerName\n    ,CustomerAddr1, CustomerAddr2, CustomerZipcode\n)\nSELECT 'IN20141000001', '2014-11-03', '20001', 'ACME MINERALS', '35 WESTCHESTER ST'\n    ,'CAIRNS, AU', '4810'\nUNION ALL\nSELECT 'IN20141000002', '2014-11-04', '20002', 'ACE CHEMICALS', '201 BROADWAY AVE'\n    ,'CAIRNS, AU', '4810';\n\nCREATE TABLE dbo.Invoice_Details\n(\n    InvoiceNo           VARCHAR(20)     NOT NULL\n    ,LineItem           BIGINT IDENTITY\n    ,ItemNo             VARCHAR(20)     NOT NULL\n    ,Quantity           INT             NOT NULL\n    ,Price              MONEY           NOT NULL\n    ,ExtendedPrice      AS (Quantity * Price)\n    ,PRIMARY KEY (InvoiceNo, LineItem)\n    ,CONSTRAINT invd_fk1 FOREIGN KEY (InvoiceNo) REFERENCES dbo.Invoices (InvoiceNo)\n);\n\n-- Seed the Invoice Details table\nINSERT INTO dbo.Invoice_Details (InvoiceNo, ItemNo, Quantity, Price)\nSELECT 'IN20141000001', '1000055', 12, 233.22\nUNION ALL SELECT 'IN20141000001', '1000056', 3, 189.33\nUNION ALL SELECT 'IN20141000002', '1000055', 1, 222.33\nUNION ALL SELECT 'IN20141000002', '1000056', 4, 123.33;\n\nSELECT *\nFROM dbo.Invoices;\n\nSELECT *\nFROM dbo.Invoice_Details;\n\n--GO\n\n--DROP TABLE dbo.Invoice_Details;\n--DROP TABLE dbo.Invoices;\n<\/pre>\n
 \tIn this sample, we have a hierarchical structure of two tables with a parent-child relationship between them, implemented using the FOREIGN KEY constraint. This is a simplification; in the real world there could be many tables originating from that single parent. In the particular case where I used this, there were a total of nine tables hanging off that same parent and the hierarchy was four levels deep. <\/p>\n
 \tThe DROPs are provided so you can clean up your sandbox later. <\/p>\n
Deleting the Data with Archiving<\/h2>\n
 \tThe only reason you’d ever need to archive deleted data is on the off-chance that someone deletes a record that shouldn’t have been deleted, and then they want it to be restored. There are alternative, traditional ways to do this: <\/p>\n