Extract from XML

Question

Below is an audit trigger what i have on each and every table.My problem is i lost my data due to some reason and only hope is to get the data back from the record which loged by the audit trigger

ALTER TRIGGER [Production].[trQuotationEntryAuditChanges] 
   ON  [Production].[QuotationEntry]
   AFTER DELETE,UPDATE
AS 
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET NOCOUNT ON;

        INSERT INTO [Security].[Audit]
            ( [OUID],[NUID],[TableName], [OldContent],[NewContent])

        SELECT 
            [OUID]=(SELECT TOP 1 [UID] FROM DELETED),   
            [NUID]=(SELECT TOP 1 [UID] FROM INSERTED),
            [TableName]=object_name([parent_obj]),
            [OldContent]=CAST((SELECT * FROM DELETED I FOR XML RAW) AS XML),
            [NewContent]=CAST((SELECT * FROM INSERTED I FOR XML RAW) AS XML)
        FROM sysobjects 
        WHERE
            [xtype] = 'tr' 
            and [name] = OBJECT_NAME(@@PROCID)
END

This is how my trigger logs data in to the table [Security].[Audit].[OldContent];Is there a way to extract data from below xml and write in to table?

<row UID="40919" ScanID="2F826E44-B0EE-41A0-9B46-7DB6E3FC1615" Quotation="2131" SLNO="273" RefNo="LP-30" ItemTemplate="336" Description="{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}{\f1 Tahoma;}}{\colortbl\red0\green0\blue0 ;\red0\green0\blue255 ;}{\*\listoverridetable}{\stylesheet {\ql\cf0 Normal;}{\*\cs1\cf0 Default Paragraph Font;}{\*\cs2\sbasedon1\cf0 Line Number;}{\*\cs3\ul\cf1 Hyperlink;}}\sectd\pard\plain\ql{\f1\fs20\cf0 MOBILE  CLEAN DISH RACK}\f1\fs20\par\pard\plain\ql{\f1\fs20\cf0 40mmX40MMX#16S.S Square Pipe upright on  150mm Dia castor two with brakes. Rubber corner Bumper On all four sides. Five (5) no.  # 18 Ga. S.S. Shelves. All sides marine edges.}\f1\fs20\par\pard\plain\ql{\f1\fs20\cf0 SIZE :}{\f1\fs20\cf0 105}{\f1\fs20\cf0 0 X}{\f1\fs20\cf0 535}{\f1\fs20\cf0  X 1900 mmH}\f1\fs20\par\pard\plain\ql\par}" Specification="0" Price="0.000000000000000e+000" Quantity="1.000000000000000e+000" InsertedOn="2013-09-28T11:01:05.157" InsertedBy="2" Host="QUOTATION" Version="AAAAAAAYfGY=" />
<row UID="40918" ScanID="06175C31-D863-4885-A3F2-0B6BDEA9A5A7" Quotation="2131" SLNO="272" RefNo="LP-26" ItemTemplate="115" Description="{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}{\f1 Tahoma;}}{\colortbl\red0\green0\blue0 ;\red0\green0\blue255 ;}{\*\listoverridetable}{\stylesheet {\ql\cf0 Normal;}{\*\cs1\cf0 Default Paragraph Font;}{\*\cs2\sbasedon1\cf0 Line Number;}{\*\cs3\ul\cf1 Hyperlink;}}\sectd\pard\plain\ql{\f1\fs20\cf0 Custom fabricated stainless steel }{\b\f1\fs20\cf0 Single Bowl Sink Unit }{\i\f1\fs20\cf0 (Wall Type)}{\f1\fs20\cf0 . Work surface 50mm high, with backsplash reinforced stainless steel top, sound deadening underneath and square tubular structure with height adjustable feet. Fabrication in }{\f1\fs20\cf1 1.5mm}{\f1\fs20\cf0  (18/10 stainless steel) }{\f1\fs20\cf1 304}{\f1\fs20\cf0  grade H.L finish sheet. (Without mixer tap &amp; fittings)}{\cf0  \line }{\f1\fs20\cf0 Size(L x H x W): 26}{\f1\fs20\cf1 00mm x 700mm x 865mm}\par}" Specification="0" Price="4.566000000000000e+003" Quantity="1.000000000000000e+000" InsertedOn="2013-09-28T11:01:05.157" InsertedBy="2" Host="QUOTATION" Version="AAAAAAAYfGU=" />
<row UID="40917" ScanID="910FDF5E-C60E-441D-95F9-D65D50E22140" Quotation="2131" SLNO="271" RefNo="LP-22" ItemTemplate="57" Description="{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}{\f1 Tahoma;}}{\colortbl\red0\green0\blue0 ;\red0\green0\blue255 ;\red32\green31\blue53 ;}{\*\listoverridetable}{\stylesheet {\ql\cf0 Normal;}{\*\cs1\cf0 Default Paragraph Font;}{\*\cs2\sbasedon1\cf0 Line Number;}{\*\cs3\ul\cf1 Hyperlink;}}\sectd\pard\plain\qj{\f1\fs20\cf2 Custom fabricated stainless steel }{\b\f1\fs20\cf2 Wall Cabinet }{\f1\fs20\cf2 with mid shelf and hinged doors, fabrication in }{\f1\fs20\cf1 304}{\f1\fs20\cf2  (18/10 stainless steel) grade H.L finish sheet. }\f1\fs20\cf2\par\pard\plain\qj{\f1\fs20\cf2 Size(L X W X H): }{\f1\fs20\cf1 1}{\f1\fs20\cf1 5}{\f1\fs20\cf1 00mm x 3}{\f1\fs20\cf1 75}{\f1\fs20\cf1 mm X 600mm}\f1\fs16\par}" Specification="0" Price="2.407500000000000e+003" Quantity="1.000000000000000e+000" InsertedOn="2013-09-28T11:01:05.157" InsertedBy="2" Host="QUOTATION" Version="AAAAAAAYfGQ=" />

Bhargav Rao · Accepted Answer · 2019-07-20 05:50:11Z

1

Try this:

SELECT A.*, t.t.value('@UID', 'int'), t.t.value('@ScanID', 'int') 
   /*, ...*/ FROM Security.Audit A CROSS APPLY 
OldContent.nodes('/row')t(t)

This article may help: Guidelines for Using xml Data Type Methods

edited Jul 20, 2019 at 5:50

Bhargav Rao

52.6k29 gold badges130 silver badges142 bronze badges

answered Sep 28, 2013 at 13:56

GriGrim

2,9411 gold badge25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Appyks Over a year ago

Excelent GriGrim!!..Thanks..Its giving the same expected result, you saved my day,Thanks :)

Collectives™ on Stack Overflow

Extract from XML

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related