SQL 2005 - Split string into sections and return values from within split sections

I'm not sure if I'm phrasing this in the best way but I have a large text string (ntext format) which is currently not indexed (I am looking into getting a full text index but I'm not going to be able to for a while at least) which is in an audit table detailing changes to data in other tables.

What I am trying to do is to pull out the old and new value for a specific table and field name, for example for this row:

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>')

(which is how my data appears in the reference table) I would like to pull the old and new values for table 1 and field 1 ONLY, giving the following output:

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开发者_如何学Go(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')

As I say, that may sound like gibberish so if there's anything I can do to clarify I'll do my best!

Thanks :)

Here is a version that works if the xml is correct.

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,
  c.a.value('OldValue[1]', 'varchar(50)') as old_value,
  c.a.value('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)

Result

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 

Edit 1 This will give you the first Attribute in the xml

select
  id,
  date_created,
  c.a.value('OldValue[1]', 'varchar(50)') as old_value,
  c.a.value('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)

Edit 2 With filter on name instead

select
  id,
  date_created,
  c.a.value('OldValue[1]', 'varchar(50)') as old_value,
  c.a.value('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 c.a.value('Name[1]', 'varchar(50)') = 'Table1'

Edit 3 Using substring instaed. Will work on your "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