StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExtract from XML
text
Body
copied!<p>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</p> <pre><code>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 </code></pre> <p><strong>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?</strong></p> <pre><code><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=" /> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload