Thanks for the help both of you - i got it working but i have 1 issue if there is multiple attachments

<Attachment>TestAnnouncement123.pdf</Attachment>

<Attachment>TestAnnouncement999.pdf</Attachment>

<Attachment>TestAnn234.pdf</Attachment>

Im inserting into Audit table

INSERT INTO [dbo].[Audit]

(Description,attachment)

SELECT

realXML.value('(/Announcement/Template)[1]', 'VARCHAR(50)') AS [Description],

realXML.value('(/Announcement/Full_News/Announcement_Details/Attachment)[1]', 'VARCHAR(100)')AS [Attachment],

FROM

(

SELECT

CONVERT(XML,CAST(AnnouncementXML AS NVARCHAR(MAX)), 2)

FROM

@t

)a

(realXML);

It will only pick up the first attachment, i know i need to do Attachment[1] , Attachment[2] up to [4] etc.. but i dont know how to incorporate it in the code....