SQL 2005 – Split String in Abschnitte und Rückgabewerte aus Split-Abschnitten

Ich bin mir nicht sicher, ob ich das in der besten Weise formuliere, aber ich habe eine große Text-String (Ntext-Format), die derzeit nicht indiziert ist (ich bin in der search nach einem Volltext-Index, aber ich werde nicht in der Lage sein zumindest für eine Weile), die sich in einer Audit-Tabelle befindet, die Änderungen in data in anderen Tabellen enthält.

Was ich versuche zu tun ist, den alten und neuen Wert für einen bestimmten Tabellen- und Feldnamen zu ziehen, zum Beispiel für diese Zeile:

CREATE TABLE #example ( id UNIQUEIDENTIFIER --unique audit id ,date_created DATETIME --date data was modified ,changes NTEXT --what parts of the data have been changed ) INSERT INTO #example VALUES ('74158983-7123-4AC8-A16A-CF88D3115B86','2006-04-05 13:30:42.993','<ChangedAttributes><Attribute><Name>Table1</Name><Field>field1</Field><Value>15980ffc-209f-46bb-8d15-31100640b5a8</value><OldValue>15980ffc-209f-46bb-8d15-31100640b5a8</OldValue></Attribute><Attribute><Name>Table2</Name><Field>field1 </Field><Value>05/04/2006</Value><OldValue </Attribute><Attribute><Name>Table2</Name><Field>Field2</Field><Value>Apple</Value><OldValue>Orange</OldValue</Attribute></ChangedAttributes>') 

(das ist, wie meine data in der referencetabelle erscheinen) Ich möchte die alten und neuen Werte für Tabelle 1 und Feld 1 NUR ziehen, geben die folgende Ausgabe:

 CREATE TABLE #output ( id UNIQUEIDENTIFIER --unique audit id ,date_created DATETIME --date data modified ,old_value VARCHAR(255) --what the data value was ,new_value VARCHAR(255) --what the data value was changed to ) INSERT INTO #output VALUES('74158983-7123-4AC8-A16A-CF88D3115B86','2006-04-05 13:30:42.993','15980ffc-209f-46bb-8d15-31100640b5a8','193B3612-551D-4EB0-B840-900F916A1BF7') 

Wie ich schon sagte, das klingt wie Kauderwelsch so, wenn es etwas gibt, was ich tun kann, um zu klären, dass ich mein Bestes tun werde!

Vielen Dank 🙂

Hier ist eine Version, die funktioniert, wenn die xml korrekt ist.

 declare @T table (id uniqueidentifier, date_created datetime, [changes] ntext) insert into @T values ( '74158983-7123-4AC8-A16A-CF88D3115B86', '2006-04-05 13:30:42.993', '<ChangedAttributes> <Attribute> <Name>Table1</Name> <Field>field1</Field> <Value>15980ffc-209f-46bb-8d15-31100640b5a7</Value> <OldValue>15980ffc-209f-46bb-8d15-31100640b5a8</OldValue> </Attribute> <Attribute> <Name>Table2</Name> <Field>field1 </Field> <Value>05/04/2006</Value> <OldValue/> </Attribute> <Attribute> <Name>Table2</Name> <Field>Field2</Field> <Value>Apple</Value> <OldValue>Orange</OldValue> </Attribute> </ChangedAttributes>') select id, date_created, cavalue('OldValue[1]', 'varchar(50)') as old_value, cavalue('Value[1]', 'varchar(50)') as new_value from (select id, date_created, cast([changes] as xml) as [changes] from @T) as T cross apply [changes].nodes('ChangedAttributes/Attribute') c(a) 

Ergebnis

 id date_created old_value new_value 74158983-7123-4AC8-A16A-CF88D3115B86 2006-04-05 13:30:42.993 15980ffc-209f-46bb-8d15-31100640b5a8 15980ffc-209f-46bb-8d15-31100640b5a7 74158983-7123-4AC8-A16A-CF88D3115B86 2006-04-05 13:30:42.993 05/04/2006 74158983-7123-4AC8-A16A-CF88D3115B86 2006-04-05 13:30:42.993 Orange Apple 

Bearbeiten 1 Dies gibt dir das erste Attribut in der xml

 select id, date_created, cavalue('OldValue[1]', 'varchar(50)') as old_value, cavalue('Value[1]', 'varchar(50)') as new_value from (select id, date_created, cast([changes] as xml) as [changes] from @T) as T cross apply [changes].nodes('ChangedAttributes/Attribute[1]') c(a) 

Bearbeiten 2 Mit Filter auf Name stattdessen

 select id, date_created, cavalue('OldValue[1]', 'varchar(50)') as old_value, cavalue('Value[1]', 'varchar(50)') as new_value from (select id, date_created, cast([changes] as xml) as [changes] from @T) as T cross apply [changes].nodes('ChangedAttributes/Attribute') c(a) where cavalue('Name[1]', 'varchar(50)') = 'Table1' 

Bearbeiten 3 Verwenden von substring instaed. Arbeitet an deinem "xml"

 select id, date_created, substring([changes], charindex('<OldValue>', [changes], 0)+10, charindex('</OldValue>', [changes], 0)-charindex('<OldValue>', [changes], 0)-10) as old_value, substring([changes], charindex('<Value>', [changes], 0)+7, charindex('</value>', [changes], 0)-charindex('<Value>', [changes], 0)-7) as new_value from @T